MySQL impossible WHERE in JOIN

Databases
1

Hello,

first, here the description of what I want to do:
There are users and roles. Users can have many roles. This relation is stored in a separate table. I want to create a search form where you can search for users with specified roles as an AND connection. Eg, you select 3 roles, the result should just be useres that have all three roles at the same time.

My tables with excerpt sampledata:


user_users
---------------------------------------
id	username	isDeleted
.......................................................
1	admin		0
11	test4		0
2	Guest		0
10	test2		0
12	Test5		0
13	Test6		0
14	Test7		0
15	Test8		0
16	Test9		0
17	Test 10		0
18	Dummy		0


roles
---------------------------------------
id 	roleName
.......................................................
1 	Administrator
2 	Guest
3 	Member
4 	Moderator
7 	Another 1
8 	Another 2
9 	Another 3

acl_usersroles
---------------------------------------
user_users_id 	acl_roles_id 	isDeleted 
.......................................................
12	2	0
1	8	0
11	2	0
11	3	0
15	2	0
14	4	0
14	8	0
14	9	0
14	2	0
16	3	0
16	4	0
16	8	0
16	9	0
16	2	0
17	4	0
17	7	0
17	2	0
1	2	0

My SQL query till now is


SELECT `user_users`.`id`, `user_users`.`username` 
FROM (`user_users`) 
JOIN `acl_usersroles` ON `acl_usersroles`.`user_users_id`=`user_users_id` 
WHERE `acl_usersroles`.`acl_roles_id` = 8 
AND `acl_usersroles`.`acl_roles_id` = 7 
AND `user_users`.`id` = `acl_usersroles`.`user_users_id`
AND `user_users`.`isDeleted` = 0 
GROUP BY `user_users`.`username` 
ORDER BY `user_users`.`username` ASC

The result is empty. If you remove the WHERE condition acl_usersroles.acl_roles_id = 7, the result is again all users with the role 8. But trying to force more than one role at the same time gives an empty result.

Does anyone have an idea how to solve this?

2

oh, i just noticed something else i overlooked the first time, and this will solve the problem

the unqualified user_users_id column in the ON clause should be user_users.id – see the difference?

:slight_smile:

3

Wow, fast answer ^^

I tried this query but the result stays the same: no results.
By removing the HAVING of course I get users that have at least one role. I tried to change to HAVING COUNT(acl_usersroles.acl_roles_id) = 2 but also no results. Even just using HAVING COUNT(*) = 1 is returning an empty set.
If the HAVING … = 8 (total number of all roles) is used, all users with one role are returned like without having.

I must be missing some connection.

4

it should have worked properly with COUNT(*) = 2

unless there is more than one user with the same username??

of course, the count has to equal the number of rows you’re filtering for, so if you want users with ( 7,8,9 ) then the count has to be 3

5 
SELECT user_users.id
     , user_users.username 
  FROM user_users
INNER 
  JOIN acl_usersroles 
    ON acl_usersroles.user_users_id = user_users_id 
   AND acl_usersroles.acl_roles_id IN ( 7,8 ) 
 WHERE user_users.isDeleted = 0 
GROUP 
    BY user_users.username 
HAVING COUNT(*) = 2
ORDER 
    BY user_users.username ASC

:cool:

6

It works! :wink:

I found that column error by myself too, but in combination of trying to solve the problem the correction didn’t work.

And the count(*) for n is also known, but still thanks for pointing that out.

In the end it was as simple as that “typo”.

Thanks