John Cobby John Cobby - 2 months ago 16
SQL Question

SQL Repeat joins different conditions

I have two tables. The first table Table0 stores a code for each item and that item's name. The second table Table1 stores a 'father' item code and then a number of 'child' item codes for each father.

Table0 Table1
itemCode | itemName fatherCode | childCode | childNum
10101 | Item 1 10101 | 20101 | 1
20101 | Sub-item 1 10101 | 20102 | 2
20102 | Sub-item 2


The purpose of the children is to show what constituent items are required to make up one of the father. Table0 also stores the codes and names of each of the children items.

I'd like to show the code and name of the father, and then also the code and name of each child next to the father.

What I'm after is something like this:

fatherCode | itemName | childCode | itemName2 | childNum
10101 | Item 1 | 20101 | Sub-item 1 | 1
10101 | Item 1 | 20102 | Sub-item 2 | 2


I guessed that it would be a simple case of just repeating the joins but with different conditions, but the itemName2 column is just repeating what is in the first itemName.

What I have tried and which didn't work:

SELECT
T0.ItemCode,
T0.ItemName,
T1.Code,
T2.ItemName,
T1.Quantity,
T1.ChildNum

FROM Table0 T0
LEFT JOIN Table1 T1 ON T1.Father = T0.ItemCode
INNER JOIN Table0 T2 ON T2.ItemName = T0.ItemName
LEFT JOIN Table1 T3 ON T3.Code = T2.ItemCode

WHERE T1.ChildNum IS NOT NULL

ORDER BY T1.ItemCode

Answer

I don't think you need any left joins here. I think it's just that you need to build the query around table1 rather than table0.

select t.fatherCode, f.itemName, t.childCode, c.itemName as itemName2, t.childNum
  from Table1 t
  join Table0 f
    on f.itemCode = t.fatherCode
  join Table0 c
    on c.itemCode = t.childCode
Comments