mssql - query with results from same table and association table



  • 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.


  • 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

    https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=6013ab43e761a362d7d3538d5a61f2ae




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2