SQL - inner - inner join

Question

Need help... I did try and almost 60% done (I guess)...

fyi: we are using DB2 and please let me know if my question is not clear.

Our original requirement was to find the PRODUCT_ID and Part_Serial_No when:

First requirement:

They install the actual_part on the actual product

Table structure was:

PRODUCT_ID          PART_NAME       PART_SERIAL_NUMBER
----------          ---------       --------------
1                   ACTUAL_PART     ACTUAL_SERIAL_1
2                   ACTUAL_PART     ACTUAL_SERIAL_2

So my SQL was simple:

SELECT PRODUCT_ID, PART_SERIAL_NUMBER FROM TABLE1 WHERE PART_NAME='ACTUAL_PART'

Second Requirement:

They changed the requirement. They said, they install a sub_part in the actual product, and the actual part will be installed on sub_part.

Table structure changed to:

PRODUCT_ID          PART_NAME       PART_SERIAL_NUMBER
----------          ---------       --------------
1                   SUB_PART        SUB_PART_SERIAL_1
SUB_PART_SERIAL_1   ACTUAL_PART     ACTUAL_SERIAL_1
2                   SUB_PART        SUB_PART_SERIAL_2
SUB_PART_SERIAL_2   ACTUAL_PART     ACTUAL_SERIAL_2

So I changed my SQL and it works:

SELECT part1.PRODUCT_ID, part2.Part_Serial_No 
from TABLE1 part1 
INNER JOIN TABLE1 part2 on part1.PART_SERIAL_NUMBER = part2.PRODUCT_ID 
where part1.PART_NAME in ('SUB_PART') 
and part2.PART_NAME in ('ACTUAL_PART')

Third Requirement:

Again they changed the requirement. They said, they install a sub_part in the actual product, and there is another sub_sub_part will be installed on sub_part. The actual part will be installed on sub_sub_part.

Table structure changed to:

PRODUCT_ID              Part_Name       PART_SERIAL_NUMBER
----------              ---------       --------------
1                       SUB_PART        SUB_PART_SERIAL_1
SUB_PART_SERIAL_1       SUB_SUB_PART    SUB_SUB_PART_SERIAL_1
SUB_SUB_PART_SERIAL_1   ACTUAL_PART     ACTUAL_SERIAL_1
2                       SUB_PART        SUB_PART_SERIAL_2
SUB_PART_SERIAL_2       SUB_SUB_PART    SUB_SUB_PART_SERIAL_2
SUB_SUB_PART_SERIAL_2   ACTUAL_PART     ACTUAL_SERIAL_2

Here I need help how to get PRODUCT_ID and PART_SERIAL_NUMBER

Answer 1

You can join the table three times, as in:

select
  a.product_id,
  b.product_id as part_id,
  c.product_id as subpart_id,
  c.part_serial_number
from table1 a
join table1 b on b.product_id = a.part_serial_number
join table1 c on c.product_id = b.part_serial_number
where a.part_name = 'SUB_PART'
  and b.part_name = 'SUB_SUB_PART'
  and c.part_name = 'ACTUAL_PART'