SQLTeam.com | Weblogs | Forums

Query question

I work on SQL server 2012 I have issue I can't get Part Id that have only map from and not have map to
depend on table #map ?

every part id must be found on table trades two times

first row for same part for map from
second row for same part for map to

meaning every part must exist two time

but if it exist as one time for part as map from and not have map to

then this

what I need to display because it not have map to

as example parts 1410,1445 have map from only so it must display

part 1348 no need to display or show because it have map from and map to

so How to write query on sql server 2012 display parts from table trades that have map from only and not have map to depend on table #map ?

create table #trades
(
PartId int,
CodeTypeId int,
Code int,
PartLevel int
)
insert into #trades(PartId,CodeTypeId,Code,PartLevel)
values
(1348,9090,13456,0),
(1348,7070,13000,1),
(1387,9090,13456,0),
(1387,7070,13000,1),
(1390,9090,13456,0),
(1390,7070,13000,1),
(1800,9095,13570,0),
(1800,7075,14000,1),
(1850,9095,13570,0),
(1850,7075,14000,1),
(1400,9090,13456,0),
(1410,9090,13456,0),
(1445,9095,13570,0),
(1485,9095,13570,0)

 create table #map
 (
 MapId int,
 CodeTypeFrom int,
 CodeTypeTo int,
 CodeValueFrom int,
 CodeValueTo int
 )
 insert into #map(MapId,CodeTypeFrom,CodeTypeTo,CodeValueFrom,CodeValueTo)
 values
 (3030,9090,7070,13456,13000),
 (3035,9095,7075,13570,14000)

Expected Result

 PartId    CodeTypeId    Code    PartLevel
 1400    9090    13456    0
 1410    9090    13456    0
 1445    9095    13570    0
 1485    9095    13570    0

What have you tried so far?

Thanks for the readily consumable data... here's one way... I'll let you change the column names to suit...

WITH
  T0 AS (SELECT * FROM #trades WHERE PartLevel = 0)
 ,T1 AS (SELECT * FROM #trades WHERE PartLevel = 1)
 SELECT T0.*,T1.*
   FROM T0
   FULL JOIN T1 ON t0.PartId = t1.PartId
  --WHERE T0.PartID IS NULL --Uncomment to see only what's missing from either side
  --   OR T1.PartID IS NULL
;