CODEWITHSUNDEEP
mysqlsql
Mstwan
Mstwan

Reputation: 13

Sql statement needed to display duplicates to 1

I recieved some help but it did not resolve my issue. I am trying to create the below table from a table that contains multiple invoices numbers and invoice codes. I want to see the invoices numbers(2 or more of the same number) for any code(multiple codes for the same invoice number is expected) I am not totally new to SQL but I am by far no expert and look for guidance. Please see below table 

 Invoice#      Inv Code 
 22567          CN
 22567          FR
 25855          CN
 25855          FR

There are other invoices and other codes in the table, but I only want those codes that have 2 or more of the same invoice number attached to them. Thnx

Upvotes: 1

Views: 115

Answers (3)

kishore krv
kishore krv

Reputation: 70

I was thinking you can self join on Invoice and get rows where invoice matches and code doesn't.

Hope this sql meets your need:

select A.invoice
FROM table1 A INNER JOIN table1 B
ON A.INVOICE=B.invoice
AND A.code<>b.CODE
GROUP BY 1

Upvotes: 0

Mihai
Mihai

Reputation: 26784

SELECT DISTINCT t1.`Inv Code` FROM table t1
join table t2 ON t1.`Inv Code`=t2.`Inv Code` 
AND t1.`Invoice#`=(SELECT `Invoice#` from table group by `Invoice#`having count(*) >1)

OK,try it now.

http://sqlfiddle.com/#!2/922f7/3

Upvotes: 0

Awais Amir
Awais Amir

Reputation: 165

I think you want to get only records which have two or more invoices of same number. You should try this

select *
from tab_name tn
where (select count(*) From tab_name where invoice = tn.invoice) > 1

Upvotes: 1

Related Questions