CODEWITHSUNDEEP
sql
user1584704
user1584704

Reputation: 27

Select records with certain value but exclude if it has another one

So I have this table

Product_ID Client_ID
1          2
1          3
2          2
3          2

Basically I need to select the product_ID's that's exclusive to client_id = 2. In my case it should return only the 2 and 3 product_ID as the one with id = 1 is not exclusive and has multiple clients setup.

Upvotes: 1

Views: 65

Answers (3)

Matt
Matt

Reputation: 15071

Use COUNT and HAVING

SELECT Product_ID
FROM ClientsProducts
GROUP BY Product_ID
HAVING COUNT(Product_ID) = 1

OUTPUT

Product_ID
2
3

SQL Fiddle: http://sqlfiddle.com/#!3/841ba7/14/0

Upvotes: 0

Mike Gledhill
Mike Gledhill

Reputation: 29161

Here's how to do it.

First, let's create your table.

create table SomeTable
(
    Product_ID int,
    Client_ID int
)
Go

insert into SomeTable values(1, 2)
insert into SomeTable values(1, 3)
insert into SomeTable values(2, 2)
insert into SomeTable values(3, 2)

The following script will return a list of all Product_ID values which have just one Client_ID value:

SELECT Product_ID
FROM SomeTable
GROUP BY Product_ID
HAVING COUNT(*) = 1

And you make this a sub-clause to get the results you're looking for:

SELECT st.Product_ID
FROM SomeTable st,
(
    SELECT Product_ID
    FROM SomeTable
    GROUP BY Product_ID
    HAVING COUNT(*) = 1
) tmp (Product_ID)
WHERE tmp.Product_ID = st.Product_ID
and st.Client_ID = 2

This will give you the row results (2 and 3) that you're looking for.

Upvotes: 1

Abhay Chauhan
Abhay Chauhan

Reputation: 414

Try this:

Select product_id from ClientsProducts where client_id in (
Select client_ID from 
(select client_ID,count(product_id) from ClientsProducts
group by client_ID
having count(product_id)=1) a )

Upvotes: 0

Related Questions