CODEWITHSUNDEEP
sqloracle-databasejoin
C LaBelle
C LaBelle

Reputation: 127

SQL: Linking Multiple Rows in Table Based on Data Chain in Select

I'm trying to create a select statement which will return all linked rows based on a "LINK_SEQUENCE" column. The column however, only links two rows together. A simple query to combine the two makes perfect sense to me. However, in the case that three or more links in the "chain" are present, I want to make sure that all codes are returned.

Table Data

How would I go about returning something similar to this when only handing the query one of the codes involved? EX: 3245.

Output Query

Not sure if it much matters in this situation, but this is for an Oracle database. Thank you all very much!

Source data from SQL Fiddle:

ID  CODE    LINK_SEQUENCE   NAME
1   3267    1               Potato
2   3245    1               Potato
3   3245    2               Potato
4   3975    2               Potato
5   3975    3               Potato
6   5478    3               Potato
7   2368    4               Apricot
8   4748    4               Apricot
9   8957    (null)          Carrot

Upvotes: 0

Views: 3231

Answers (1)

PM 77-1
PM 77-1

Reputation: 13344

SELECT * FROM LinkedTable lt
WHERE ft.link_sequence IN 
   ( SELECT link_sequence FROM LinkedTable WHERE code = 3245 AND link_sequence IS NOT NULL ) 
ORDER BY ft.ID;

See my SQL Fiddle DEMO.

SECOND ATTEMPT:

SELECT DISTINCT * 
FROM LinkedTable
START WITH code = 3245
CONNECT BY NOCYCLE
           PRIOR code = code  AND PRIOR link_sequence+1 = link_sequence OR
           PRIOR code <> code AND PRIOR link_sequence =   link_sequence
ORDER BY link_sequence, code
;

Updated SQL Fiddle with this code. Please try to break it.

Based on your data (starting with 3245) it gives the following chain:

ID  CODE    LINK_SEQUENCE   NAME
2   3245    1              Potato
1   3267    1              Potato
3   3245    2              Potato
4   3975    2              Potato
5   3975    3              Potato
6   5478    3              Potato

Upvotes: 1

Related Questions