SQL: Issue with finding unique customers of shops

Question

I am new to SQL, I want to write a query to find the unique customers to any particular shop. For example: Shop SH1 is having 10 customers registered, out of 10 - 3 customers also registered to some other shops. I want a query which will return the unique 7 customers to shop SH1.

Both the ShopId and CustomerId are stored in same table, so I guess a subquery will be required.

Thanks, Mayur

Sample Data:

ShopId CustomerId

Shop1 Cust001
Shop1 Cust002
Shop1 Cust003
Shop2 Cust002
Shop3 Cust004
Shop4 Cust002

In above example, if I run the query for shop id Shop1, I should get Cust001, Cust003 in return which are unique to Shop1 and not Cust002 as it is linked with other shop ids.

Can you show your table structure and some sample data (What you have and what you expect)? — Arion, Mar 13 '13 at 09:02

Ajo Koshy · Accepted Answer · 2013-03-13T09:50:21.243

1

I guess you could do this

select distinct customerId from tablename where shopId = 'SH1' and customerId not in (select customerId from tablename where shopId<>'SH1');

edited Mar 13 '13 at 09:50

answered Mar 13 '13 at 08:59

Ajo Koshy

1,205
2
21
33

This will give all 10 customer ids, while my requirement is to find only 7 customer ids (as per example above) which are not linked with any other shop id. – Mayur Jadhav Mar 13 '13 at 09:07
1

@user1696688 Can you try with this query? `select distinct customerId from tablename where shopId = 'SH1' and customerId not in (select customerId from tablename where shopId<>'SH1');` – Ajo Koshy Mar 13 '13 at 09:10
1

@user1696688, Ajo Koshy: Guys, if the accepted suggestion is the one made in the comment, someone of you should edit it in to the answer. Otherwise accepting the answer as it is at the moment makes no sense and is thus **very** confusing. – Andriy M Mar 13 '13 at 09:46

score 1 · Answer 2 · answered Mar 13 '13 at 09:12

If you want to know the store on which they are registered too

SELECT CustomerID, 
    ShopID
    FROM shop
    WHERE CustomerID IN (
        SELECT CustomerID
            FROM shop 
            GROUP BY CustomerID 
            HAVING COUNT(ShopID) = 1)

score 0 · Answer 3 · answered Mar 13 '13 at 09:06

0

You need unique customers

SELECT CUSTID FROM SHOP GROUP BY CUSTID HAVING( COUNT(SHOPID) = 1)

answered Mar 13 '13 at 09:06

Tony Shih

430
5
6

SQL: Issue with finding unique customers of shops

3 Answers3