question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked MelissaMa-msft edited

How to get parts have duplicate on code type and different on code ?

I work on sql server 2012 I need to make select query display parts have duplicate on code type and different on code

 CREATE TABLE #Trades
 (
 PartId int,
 CodeTypeId int,
 Code Int
 )
 insert into #Trades(PartId,CodeTypeId,Code)
 values
 (1215,1220,250),
 (1215,1220,110),
 (1350,1220,330),
 (1350,1220,900),
 (4521,2500,700),
 (4521,2500,800),
 (4521,2500,950),
 (3500,2900,230),
 (3500,2900,230),
 (5400,1220,230),
 (5400,1220,230)

expected result
101999-image.png




expected result text as image above

 PartId    CodeTypeId    Code
 1215    1220    250
 1215    1220    110
 1350    1220    330
 1350    1220    900
 4521    2500    700
 4521    2500    800
 4521    2500    950
sql-server-generalsql-server-transact-sql
image.png (6.5 KiB)
· 2
5 |1600 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.

If you add one more line such as 5400, 1220, 330, would you expect to see 3 lines in the output or what would be your output?

0 Votes 0 ·

Hi @ahmedsalah-1628

Could you please validate and provide any update?

Besides, what is the expected output of below data? Will partid 5400 be appear in the output?

  CREATE TABLE #Trades
  (
  PartId int,
  CodeTypeId int,
  Code Int
  )
  insert into #Trades(PartId,CodeTypeId,Code)
  values
  (1215,1220,250),
  (1215,1220,110),
  (1350,1220,330),
  (1350,1220,900),
  (4521,2500,700),
  (4521,2500,800),
  (4521,2500,950),
  (3500,2900,230),
  (3500,2900,230),
  (5400,1220,230),
  (5400,1220,230),
  (5400,1220,240)

Best regards,
Melissa

0 Votes 0 ·

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @ahmedsalah-1628

Please refer below:

 select * from  #Trades
 where PartId in (
 select PartId from #Trades
 group by PartId,CodeTypeId
 having count(distinct code) >1)

OR

 ;with cte as (
 select PartId,CodeTypeId from #Trades
 group by PartId,CodeTypeId
 having count(distinct code) >1)
 select a.* 
 from #Trades a
 inner join cte b 
 on a.PartId=b.PartId and a.CodeTypeId=b.CodeTypeId

Output:

 PartId    CodeTypeId    Code
 1215    1220    250
 1215    1220    110
 1350    1220    330
 1350    1220    900
 4521    2500    700
 4521    2500    800
 4521    2500    950

If both of above are not working, please provide more sample data and expected output.

Best regards,
Melissa


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.

5 |1600 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.