mssql – query with results from same table and association table

The question:

I need help with a query.

I have 3 tables:

  1. “Parts” table.
    one important thing to note here is that, the parts table contains both main part and child part. Child parts are components of main part. The child part is linked to main part using a column “MainPartId”

here is the table

Id Name PartNumber CTypeId MainPartId VendorId
1 transmission 01 TR001 1010 0 V1
2 gear 4324 GR01-4324 441 1 V1
3 gear 223 GR11-223 442 1 V1
4 gasket 56 GS7-56 10 1 V1
5 motor 01 CS001 1010 0 V1
6 gear 378 GR01-378 441 5 V1

explanation:
a) “transmission 01” (with Id value of 1), and “motor 01” (with Id value of 5) are main parts. main part always has “mainPartId” value as 0. And CTypeId as 1010
b) 2,3,4 rows are child parts of “transmission 01” , hence MainPartId is “1” (Id of “transmission 01”). Similarly 6 is a child part of “motor 01”
c) CType Id does NOT repeat for a given main part. ie: from the above table, you will see “gear 4324” has CTypeId od “441”. this will not repeat for any child part of “transmission 01”. But another main part -like “motor 01”- can have child part with CtypeId “441” as shown in the table

  1. “Equipment” table
Id Name EquipmentNumber
1 Heavy Eqp EQL335
2 Light Eqp EQL873
  1. “EquipmentPartAssoc” table

This is a table which contains the association for equipment-parts

Id EquipmentId PartId
1 1 1
2 1 5
3 2 5

About the query and expected result:
The result I want is to list all mainpart – childpart combo with specific CTypeId for each equipment.
eg (if I want results for CtypeId 441 and vendor v1)

EquipmentNumber PartNumber ChildPartNumber CTypeId VendorId
EQL335 TR001 GR01-4324 441 V1
EQL335 CS001 GR01-378 441 V1
EQL873 CS001 GR01-378 441 V1

additional notes:

  1. we do not query for CTypeId 1010, so no need to consider that case
  2. In this case, only 1 vendor “V1” is given in the table. But I hope you get the point that there can be many vendors.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

Using inner join, starts from the Parts table and self join to get the Parent’s information.

select e.EquipmentNumber,
       p.PartNumber,
       ChildPartNumber = c.PartNumber,
       c.CTypeId,
       c.VendorId
from   Parts c   -- child
       inner join Parts p on c.MainPartId = p.Id    -- parent
       inner join EquipmentPartAssoc a on p.Id = a.PartId
       inner join Equipment e on e.Id = a.EquipmentId
where  c.CTypeId  = 441
and    c.VendorId = 'V1'

Result :

EquipmentNumber PartNumber ChildPartNumber CTypeId VendorId
EQL335 TR001 GR01-4324 441 V1
EQL335 CS001 GR01-378 441 V1
EQL873 CS001 GR01-378 441 V1

db<>fiddle demo


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment