CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
RJP
RJP

Reputation: 4116

Writing a query to select specific foreign key entries given a specific criteria

So I have a table that has a FK to a UserId, and a FK to a ProductId.

Each UserId can be listed multiple times if they have multiple products.

I need to query to get each UserId that has, for example, ProductId 1, but NOT ProductId 2.

How can I get started writing a query like this?

Edit:

But here is the part I'm having problems with; all users who have Product 1 should have Product 2, but some don't, so I need to get the users who have Product 1 and NOT Product 2

Upvotes: 1

Views: 128

Answers (3)

T I
T I

Reputation: 9933

assuming you have a bridge table between user and product

if you want more then the id

SELECT u.user_id, u.name
FROM [user] AS u
INNER JOIN user_product AS up 
        ON u.user_id = up.user_id
INNER JOIN product as p 
        ON p.product_id = up.product_id
WHERE p.id = 1

or you can just query your bridge table

SELECT up.user_id
FROM user_product AS up
WHERE up.product_id = 1

Upvotes: 2

Landi
Landi

Reputation: 661

-- Is this what you want?

SELECT 
    u.[UserID]
  , p.[ProductID]
FROM TableA a
    LEFT JOIN Users u ON
        u.[UserID] = a.[UserID]
    LEFT JOIN Products p ON
        p.[ProductID] = a.[ProductID]
WHERE a.ProductID = 1

Upvotes: 2

valex
valex

Reputation: 24144

select distinct userID from yourtable t
where t.ProductId=@ProductID1
and not exists(select UserID from yourtable 
                      where Userid=t.userID and ProductID=@ProductID2)

Upvotes: 2

Related Questions