mssql – query with results from same table and association table

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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 Reply