How to find doubles with SQL?

Question

Using the Chinook Test Database I wrote this SQL statement to show all the tracks ordered by two specific customers:

SELECT inv.BillingCity,cus.LastName,tra.Name
        FROM invoice AS inv
        JOIN customer AS cus ON inv.CustomerId=cus.CustomerId
        JOIN invoiceline inl ON inv.InvoiceId=inl.InvoiceId
        JOIN track tra ON tra.TrackId=inl.TrackId
        WHERE cus.LastName IN ('Schneider','Schröder')
        ORDER BY inv.BillingCity,cus.LastName,tra.Name

I see that there is a track that was ordered twice by one customer:

enter image description here

How would I write an SQL statement to find doubles like this, i.e. "return all tracks ordered multiple times by one customer"?

select BillingCity, lastName, Name from invoice groupe by BillingCity, lastName, Name having count(*) >1 — Json, Sep 21 '18 at 13:23

score 2 · Accepted Answer · edited Jul 07 '12 at 05:32

2

Try this:

SELECT cus.CustomerId,tra.Name,COUNT(cus.CustomerId) AS tot
       FROM invoice AS inv
       JOIN customer AS cus ON inv.CustomerId=cus.CustomerId
       JOIN invoiceline inl ON inv.InvoiceId=inl.InvoiceId
       JOIN track tra ON tra.TrackId=inl.TrackId
       GROUP BY cus.CustomerId,tra.Name
       HAVING tot > 1

edited Jul 07 '12 at 05:32

Edward Tanguay

189,012
314
712
1,047

answered Jul 07 '12 at 05:24

Marco

56,740
14
129
152

select BillingCity, lastName, Name from invoice groupe by BillingCity, lastName, Name having count(*) >1 – Json Sep 21 '18 at 13:23

How to find doubles with SQL?

1 Answers1