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.


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.