How to select a related group of items in Oracle SQL

Question

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.

Answer 1

SELECT * FROM table_name WHERE ID=your_id UNION ALL SELECT * FROM table_name WHERE ID='MASTER' AND link = (SELECT link FROM table_name WHERE ID=your_id)

That should answer to the question I understood ;)

Answer 2

If I understand correctly, here's an example I think does what you are looking for...

select * from my_table where link in (select link from my_table where id = 'AA') and id in ('AA','MASTER')

Answer 3

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

Answer 4

What i understand is that you what all the records that have the same "link" as the one with id = AA firts you need the link field:

select i.link from table i
where i.id = YOUR_ID

now you "select" the records that have that link

select * from table 
where link in (select i.link from table i where i.id = YOUR_ID)

This select should give you the records you need...