CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
lwb
lwb

Reputation: 389

Combine multiple conditions with Having clause

Given this user and User's items table:

User Table enter image description here

With this select below I'm able to get the candidates that have both item 3 and 4:

Select 
     U.Id, 
     U.[Name],
     STRING_AGG(UI.ItemId, ',') as Items
from Users U
     Left Join UserItems UI on UI.UserId = U.Id
Where (
     UI.ItemId IN (3, 4)
)
Group By U.Id, U.[Name]
Having Count(*) = 2

But how can I get all candidates using more conditions? For example:

UI.ItemId IN (2, 7) OR UI.ItemId IN (3,4) OR UI.ItemId = 2

Any idea on how to solve this?

Upvotes: 0

Views: 2920

Answers (5)

Illyrian
Illyrian

Reputation: 1

If you want to get all users with their ItemIds try this:

Select 
     U.Id, 
     U.[Name],
     STRING_AGG(UI.ItemId, ',') as Items
from Users U
     left JOIN UserItems UI on UI.UserId = U.Id
Group By U.Id, U.[Name]

You should remove 'where clause' if you want all users. Do not use 'Having clause' in this case

Upvotes: 0

Sebastian S.
Sebastian S.

Reputation: 1391

DECLARE @ids TABLE (id INT)
INSERT INTO @ids
VALUES (1), (3)

Select 
     U.Id, 
     U.[Name],
     STRING_AGG(UI.ItemId, ',') as Items
from Users U
     Left Join UserItems UI on UI.UserId = U.Id
     INNER JOIN @ids I on UI.ItemId = I.id
Group By U.Id, U.[Name]
HAVING(COUNT(*) = (SELECT COUNT(*) FROM @ids))

Upvotes: 0

Akina
Akina

Reputation: 42632

SELECT ...
FROM ...
GROUP BY U.Id, U.[Name]
HAVING 2 = COUNT(DISTINCT CASE WHEN UI.ItemId IN (3, 4) THEN UI.ItemId END)
   AND 3 = COUNT(DISTINCT CASE WHEN UI.ItemId IN (5, 6, 7) THEN UI.ItemId END)
   AND ...

UI.ItemId IN (2, 7) OR UI.ItemId IN (3,4) OR UI.ItemId = 2

HAVING 2 = COUNT(DISTINCT CASE WHEN UI.ItemId IN (2, 7) THEN UI.ItemId END)
    OR 2 = COUNT(DISTINCT CASE WHEN UI.ItemId IN (3, 4) THEN UI.ItemId END)
    OR 1 = COUNT(DISTINCT CASE WHEN UI.ItemId IN (2) THEN UI.ItemId END)

If the condition means "one of 3 listed conditions, and no records out of matched condition", then modify each separate into

HAVING (     2 = COUNT(DISTINCT CASE WHEN UI.ItemId IN (2, 7) THEN UI.ItemId END)
         AND 0 = COUNT(DISTINCT CASE WHEN UI.ItemId NOT IN (2, 7) THEN UI.ItemId END)
       )
   OR ...

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269743

First, you are looking for matches between the two tables. Use a JOIN rather than LEFT JOIN.

You can use a more complex having clause:

having (sum(case when ui.itemid = 2 then 1 else 0 end) > 0 and
        sum(case when ui.itemid = 7 then 1 else 0 end) > 0
       ) or
       (sum(case when ui.itemid = 3 then 1 else 0 end) > 0 and
        sum(case when ui.itemid = 4 then 1 else 0 end) > 0
       ) or
       sum(case when ui.itemid = 2 then 1 else 0 end) > 0

Of course, the first condition is a subset of the last condition, so it is not necessary. But this illustrates the basic idea.

If you know you have no duplicates -- as your original query suggests -- then I would also suggest:

having sum(case when ui.itemid in (2, 7) then 1 else 0 end) = 2 or
       sum(case when ui.itemid in (3, 4) then 1 else 0 end) = 2 or
       sum(case when ui.itemid = 2 then 1 else 0 end) = 1

Upvotes: 2

GMB
GMB

Reputation: 222452

You can use aggregation as follows:

select
    u.id,
    u.name,
    string_agg(i.itemId, ',') items
from users u
left join userItems i on i.userId = u.id
group by u.id, u.name
having 
    max(case when i.itemId = 3 then 1 end) = 1
    and max(case when i.itemId = 4 then 1 end) = 1

You can easily expand the having clause with more combinations, like:

having 
    -- users that have both items 3 and 4
    (
        max(case when i.itemId = 3 then 1 end) = 1
        and max(case when i.itemId = 4 then 1 end) = 1
    )
    -- users that have both items 2 and 7
    or (
        max(case when i.itemId = 2 then 1 end) = 1
        and max(case when i.itemId = 7 then 1 end) = 1
   )
    -- users that have items 1
   or max(case when i.itemId = 1 then 1 end) = 1

Upvotes: 0

Related Questions