CODEWITHSUNDEEP
mysqlsqldatabase
divHelper11
divHelper11

Reputation: 2208

Select HAVING with multiple arguments

I have users table and regulations table.

For each user, there are 4 rows on each regulation.

For example: user_id 1 accepted all the regulations so he will have records like that in regulations table:

  1. user_id = 1, regulation = 1, accepted = 1
  2. user_id = 1, regulation = 2, accepted = 1
  3. user_id = 1, regulation = 3, accepted = 1
  4. user_id = 1, regulation = 4, accepted = 1

Now what I want to do is to select all users, who accepted all of the regulations.

Not exactly sure how I should construct the HAVING part of this query.

Here is my take but I am almost sure it is wrong, in addition it says it doesnt see the regulations.regulation column:

SELECT users.* FROM users 
JOIN regulations ON regulations.user_id = users.id
GROUP BY regulations.user_id
HAVING
    (regulations.regulation = 1 AND regulations.accepted = 1)
and (regulations.regulation = 2 AND regulations.accepted = 1)
and (regulations.regulation = 3 AND regulations.accepted = 1)
and (regulations.regulation = 4 AND regulations.accepted = 1)

Question: What is the proper way to select all users who accepted = 1 on all 4 regulations?

Upvotes: 0

Views: 608

Answers (2)

Koen
Koen

Reputation: 734

You can do as below:

SELECT users.id FROM users 
     INNER JOIN regulations ON regulations.user_id = users.id
WHERE regulations.accepted = 1
GROUP BY users.id
HAVING count(distinct regulations.regulation) = 4

It selects all users.id from the users table where the number of different accepted regulations is 4. The count distinct in the 'HAVING' clause is to make certain that if the same regulation is present twice, it only gets counted once. What it does not check is if the accepted regulations are one of 1,2,3 or 4.

Another option using a nested query rather than an inner join for the same effect:

SELECT * from users u
WHERE 4 = ( SELECT count(distinct regulation) 
            FROM regulations r
            WHERE r.user_id=u.id and r.accepted=1 )

And a final one (that does depend on having the different values for each regulation) without WHERE or HAVING clauses and no COUNT:

SELECT u.* from users u
   INNER JOIN regulations r1 ON r1.user_id=u.id and r1.accepted=1 and r1.regulation=1
   INNER JOIN regulations r2 ON r2.user_id=u.id and r2.accepted=1 and r2.regulation=2
   INNER JOIN regulations r3 ON r3.user_id=u.id and r3.accepted=1 and r3.regulation=3
   INNER JOIN regulations r4 ON r4.user_id=u.id and r4.accepted=1 and r4.regulation=4

See also the tests: Fiddle

Upvotes: 4

Fahmi
Fahmi

Reputation: 37473

Try below query with where clause instead of having clause:

SELECT users.* FROM users 
JOIN regulations ON regulations.user_id = users.id
where regulations.accepted = 1 and regulations.regulation in (1,2 ,3 ,4)
GROUP BY regulations.user_id having count(distinct regulations.regulation)=4

Upvotes: 2

Related Questions