How to get PartId from table #trades where it have parts that have only map To and not have map From depend on table #map ? RSS

• • Reply
  ahmedbarbary

  Member

  41 Points

  376 Posts

  How to get PartId from table #trades where it have parts that have only map To and not have map F...

  Mar 07, 2021 11:24 PM|ahmedbarbary|LINK

  I work on SQL server 2012 I have issue I can't get Part Id that have only map

  To and not have map From

  depend on table #map ?

  every part id must be found on table trades two times

  first row for same part for map from code type from and code value from
  second row for same part for map to code type to and code value to

  meaning every part must exist two time

  but if it exist as one time for part as map To code type and code value

  and not have map From code type and code value

  then this

  what I need to display because it not have map From

  as example parts 1410,1445,1445,1485,1348,1850 have map To 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 To only and not have map From 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,0),
   (1387,9090,13456,0),
   (1387,7070,13000,0),
   (1390,9090,13456,0),
   (1390,7070,13000,0),
   (1800,9095,13570,0),
   (1800,7075,14000,0),
   (1850,9095,13570,0),
   (1850,7075,14000,0),
   (1400,7070,13000,0),
   (1410,7070,13000,0),
   (1445,7075,14000,0),
   (1485,7075,14000,0),
   (1348,7075,14000,0),
   (1850,7070,13000,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
  
   TradeCodesId    PartId    CodeTypeId    Code    PartLevel
   11    1400    7070    13000    0
   12    1410    7070    13000    0
   13    1445    7075    14000    0
   14    1485    7075    14000    0
   15    1348    7075    14000    0
   16    1850    7070    13000    0

• • Reply
  Sean Fang

  Contributor

  3020 Points

  891 Posts

  Re: How to get PartId from table #trades where it have parts that have only map To and not have m...

  Mar 08, 2021 05:44 AM|Sean Fang|LINK

  Hi ahmedbarbary,

    

  Here I would like to provide you with an idea about how to produce the desired output.

  1. Select all of data mappings (create a temp table #temp)
  2. Select all of rows from #trades with mapping
  3. Exclude the result of step 2 from the #trades table

    

  More details, you could refer to below codes.

  DROP TABLE IF EXISTS #trades
  DROP TABLE IF EXISTS #map
  DROP TABLE IF EXISTS #temp
  
  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,0),
   (1387,9090,13456,0),
   (1387,7070,13000,0),
   (1390,9090,13456,0),
   (1390,7070,13000,0),
   (1800,9095,13570,0),
   (1800,7075,14000,0),
   (1850,9095,13570,0),
   (1850,7075,14000,0),
   (1400,7070,13000,0),
   (1410,7070,13000,0),
   (1445,7075,14000,0),
   (1485,7075,14000,0),
   (1348,7075,14000,0),
   (1850,7070,13000,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)
  
  
  SELECT c.PartId, c.CodeTypeFrom, c.CodeTypeTo INTO #temp FROM 
  (
  SELECT a.PartId, a.CodeTypeId AS CodeTypeFrom, b.CodeTypeId AS CodeTypeTo FROM #trades a
  JOIN #trades b ON a.CodeTypeId != b.CodeTypeId AND a.PartId = b.PartId
  ) c
  JOIN #map d ON c.CodeTypeFrom = d.CodeTypeFrom AND c.CodeTypeTo = d.CodeTypeTo
  
  
  -- select all of data mappings
  -- SELECT * FROM #temp
  
  -- SELECT All of rows from #trades with mapping
  -- SELECT * FROM #trades a
  -- JOIN #temp b ON a.PartId = b.PartId AND (a.CodeTypeId = b.CodeTypeFrom OR a.CodeTypeId = b.CodeTypeTo)
  
  -- Final result
  SELECT * FROM #trades 
  EXCEPT (
  SELECT a.PartId, a.CodeTypeId,a.Code,a.PartLevel FROM #trades a
  JOIN #temp b ON a.PartId = b.PartId AND (a.CodeTypeId = b.CodeTypeFrom OR a.CodeTypeId = b.CodeTypeTo)
  )
  

  Result:

  

    

  Hope helps.

  Best regards,

  Sean

  ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >