How to select a related group of items in Oracle SQL

I have some data of the form

Key    ID      Link       
1      MASTER  123
2      AA      123
3      AA      123
4      BB      123
5      MASTER  456
6      CC      456

I would like to be able to select in the same select all linked items matching the selection criteria, plus the linked master. For example, if I have an ID of 'AA', I want the rows with ID = 'AA' to be returned, plus the row with ID = 'MASTER' and a link of 123:

1      MASTER  123
2      AA      123
3      AA      123

I'm using Oracle 10.2g, so if any special Oracle syntax will make this easier, then that would be ok.

Answers


Here's one method.

SELECT DISTINCT key, id, link
  FROM the_table
  START WITH id = 'AA'
  CONNECT BY id = 'MASTER' and link = PRIOR link and 'AA' = PRIOR ID

Need Your Help

CodeIgniter: adding a $subresult to an object $result by an ActiveRecord result ID

php codeigniter object activerecord

I'm having issues adding a "sub result" to a result in Codeigniter. Not sure how to add to this object.

Application of Shared Read Locks

concurrency locking abap

what is the need for a read shared lock?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.