question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked ·

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 ?

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



sql-server-generalsql-server-transact-sql
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please update a few cases published last week, thank you!

0 Votes 0 ·

1 Answer

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered ·

Hi @ahmedsalah-1628

For different CodeTypeTo with the same PartId, you need to provide a fixed order to return the result you expect.

For different CodeTypeTo with the same PartId, you need to provide a fixed order to return the result you expect. The data you provide is actually in no order. The first row and the second row you mentioned are just data returned by sql server randomly. Therefore, currently only the following results can be achieved, that is, for PartIds with two CodeTypeTos, only one CodeTypeTo can be returned randomly:

     ;with except_all as
      (select row_number() 
              over(partition by PartId
        order by(select 0)) as rn, PartId
        from (select PartId from #trades
      where CodeTypeId in (select CodeTypeTo from #map)) t
      except
      select row_number() 
              over(partition by PartId
        order by(select 0)) as rn, PartId
        from (select PartId from #trades
     where CodeTypeId in (select CodeTypeFrom from #map)) t)
     ,cte2 as
     (select e.PartId,t.CodeTypeId,t.code,t.PartLevel,row_number() over(partition by t.PartId order by t.PartId) rr 
     from  except_all e
     join #trades t on e.PartId=t.PartId
     where t.CodeTypeId not in (select CodeTypeFrom from #map))
        
     select PartId,CodeTypeId,code,PartLevel from cte2 
     where rr<2

Output:
75221-image.png

Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




image.png (6.6 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.