CODEWITHSUNDEEP
sqlhierarchical-datarecursive-query
JustinW
JustinW

Reputation: 3

Find all parent and child with a relationship table

I have inherited two tables:

Table 1: Loc

LocID    Field1    Field2
---------------------------
 1       AAA       BBB
 2       CCC       DDD
 3       EEE       FFF
 4       GGG       HHH
 5       III       JJJ
 6       KKK       LLL
 7       MMM       NNN
 8       OOO       PPP
 9       QQQ       RRR
10       SSS       TTT
11       UUU       VVV

Table 2: LocRel

LocID_A     LocID_B
-----------------------
   1           2
   1           3
   2           4
   2           5
   3           6
   3           7
   5          11
   8           9
   8          10

So, the LocRel table basically is used to specified the relationships between the various records in the Loc table.

I am struggling with writing a sql statement that will list all the relationships like so:

LocID   Level   LocIDH_Ancestry    Field1    Field2
---------------------------------------------------
  1       1        NULL            AAA       BBB
  2       2        1               CCC       DDD
  3       2        1               EEE       FFF
  4       3        1,2             GGG       HHH
  5       3        1,2             III       JJJ
  6       3        1,3             KKK       LLL
  7       3        1,3             MMM       NNN
  8       1        NULL            OOO       PPP
  9       2        8               QQQ       RRR
 10       2        8               SSS       TTT
 11       4        1,2,5           UUU       VVV

I am not good at all in queries involving relationships, and would really appreciate some help on how the above can be achieved.

Thank you to all!

Upvotes: 0

Views: 106

Answers (1)

Fede
Fede

Reputation: 4016

This answer may be SQL Server specific. I'm not an expert in SQL, so I don't know how much of this is standard and/or adopted in other dbms.

You can implement the kind of queries you mentioned, using recursive queries. Here is a intro to the subject that provides a good starting point.

For your specific query, something like this should do the work.

WITH MyCTE AS
(
   SELECT
       LocID, 1 as Level,
       NULL as LocIDH_Ancestry,
       Field1, Field2
   FROM
       Table1
   WHERE
       LocID NOT IN (SELECT LocID_B FROM Table2)

   UNION ALL

   SELECT
       t1.LocID,
       c.Level + 1 as Level,
       t2.LocID_A as LocIDH_Ancestry,
       t1.Field1, t1.Field2
   FROM
       Table1 t1
   INNER JOIN
       Table2 t2 ON t1.LocID = t2.LocID_A
   INNER JOIN
       MyCTE c ON t2.LocID_B = c.LocID
)
SELECT *
FROM MyCTE

Hope that helps.

Upvotes: 1

Related Questions