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