CODEWITHSUNDEEP
sqlsql-server
mrmcg
mrmcg

Reputation: 193

Joined 3 Tables in SQL and Getting duplicated records

I have 3 tables and trying to get the data out without having many duplicates. Please check on the image that I sent. This is the desired result. Thanks.

I tried:

EDIT:

All this does is Grab all records from Parts then all the records from Assemblies then all the records in Materials. So the return records is HUGE. Here is the standard SQL Statement that connects these tables together.

SELECT impPartID, impShortDescription, uimpConfigPartID as ConfigID, 
imaPartID, immPartID FROM Parts
Left Outer Join PartAssemblies on IMAMETHODID = IMPPARTID and 
IMAPARENTASSEMBLYID = 0
Left Outer Join PartMaterials on IMMMETHODID = IMPPARTID and 
IMMMETHODASSEMBLYID = 0

So I tried this

Select p.impPartID, p.impShortDescription, p.uimpConfigPartID as ConfigID, 
a.imaPartID as Assem, m.immPartID as Materials
from (select p.*, row_number() over (order by (select null)) as seqnum
 from parts p where uimpConfigPartID <> ''
 ) p full join
 (select a.*, row_number() over (order by (select null)) as seqnum
  from assemblies a
 ) a
 on a.IMAMETHODID = p.impPartID and a.seqnum = p.seqnum full join
 (select m.*, row_number() over (order by (select null)) as seqnum
  from materials m
 ) m
 on m.IMMMETHODID = coalesce(a.IMAMETHODID, p.impPartID) and
 m.seqnum = coalesce(a.seqnum, p.seqnum)

3 Tables with the desired result

Upvotes: 0

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

One method uses row_number(), union all and group by:

select p.partid, a.descassembly, m.descmaterial
from (select p.*, row_number() over (order by (select null)) as seqnum
      from parts p
     ) p full join
     (select a.*, row_number() over (order by (select null)) as seqnum
      from assemblies a
     ) a
     on a.partid = p.partid and a.seqnum = p.seqnum full join
     (select m.*, row_number() over (order by (select null)) as seqnum
      from materials m
     ) a
     on m.partid = coalesce(a.partid, p.partid) and
        m.seqnum = coalesce(a.seqnum, p.seqnum);

Upvotes: 1

Related Questions