CODEWITHSUNDEEP
sql
Carlos
Carlos

Reputation: 377

Guidance creating a SQL query

I am trying to create a query that returns userIds that have not received an offer. For example I would like to offer the productId 1019 to my users but I do not want to offer the product to users that already received it. But the query below keeps returning the userId = 1054 and it should only return userId=3333. I will appreciate any help.

Users:

  Id        Status
   -----------------
  1054       Active
  2222       Active
  3333       Active

Offers:

 userId   ProductId
 --------------------
 1054       1019
 1054       1026
 2222       1019
 3333       1026

Query

DECLARE @i int = 1019

SELECT Distinct c.id 
FROM Users c  
INNER JOIN offers o ON c.id = o.UserId  
WHERE o.ProductId NOT IN (@i) 
ORDER BY c.id

Upvotes: 0

Views: 47

Answers (3)

micbobo
micbobo

Reputation: 872

I think what is lacking is you should use a left join instead of an inner join

Go check the example here : http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_join_left

If you replace,in the example in the link, the ORDER BY with a WHERE Orders.OrderID IS NULL you get something similar to what you are looking for. Run the SQL in there and try different things with their data it will help you a lot a lot understanding how the different types of join work

Upvotes: 0

Bob
Bob

Reputation: 1055

Here is a SQLFiddle to show how it works: http://sqlfiddle.com/#!6/cb79b/3

select
  c.id
from
  Users c
where
  c.id not in (
    select
      o.userid
    from
      Offers o  
    where o.ProductId = @i
  )
order by c.id

Upvotes: 3

fthiella
fthiella

Reputation: 49049

You can use a LEFT JOIN:

DECLARE @i int = 1019

SELECT c.id
FROM
  Users c LEFT JOIN offers o  
  ON c.id = o.UserId  
  AND o.ProductId IN (@i)
WHERE
  o.UserId IS NULL
ORDER BY
  c.id

Please see an example here.

Upvotes: 3

Related Questions