[SOLVED] MySQL query with multiple ANDs for same column

Databases
1

Hi,

I have the following tables:

clients
client_id | client_name
1 | client1
2 | client2
3 | client3
…

tags
tag_id | tag_name
1 | tag1
2 | tag2
3 | tag3
…

client_tags
id | id_client | id_tag
1 | 1 | 1
2 | 1 | 2
3 | 2 | 2
4 | 3 | 1
…

I am trying to write a SELECT query to select the clients who are tagged by both tag1 and tag2. In the above setup, my query should return client1.

I have the following query at the moment:

SELECT * FROM clients LEFT JOIN client_tags ON client_id = id_client LEFT JOIN tags ON tag_id = id_tag WHERE tag_id = 1 AND tag_id = 2

The above query returns nothing. I know my use of WHERE … AND is not correct, but I don’t know how to fix it to get the result I want.

Thanks for any ideas.

2

AND should be OR :slight_smile:

3

OR won’t work (tested it), because it will return all clients who has either tag1 or tag2, which are client1, client2, client3.

4

Maybe this will do the job… I am not the greatest with query stuff so maybe someone can confirm or give better answer.

select *, count(*) from client_tags ct
join clients c on c.id = ct.client_id
where ct.tag_id in (select id from tags where tag_name = 'tag1' or tag_name = 'tag2')
group by ct.client_id
having count(*) > 1
1 Like
5

SOLVED

Thank you very much, after some further digging, I was able to come up with the following, similar to yours, which seems to be doing exactly what I needed:

SELECT * FROM clients
LEFT JOIN client_tags ON client_id = id_client
LEFT JOIN tags ON tag_id = id_tag
WHERE tag_id IN ('tag1', 'tag2')
GROUP BY client_id
HAVING COUNT(*) = 2

I am passing (‘tag1’, ‘tag2’) and the count value 2 -the number of tags to match- programmatically.

Thanks.

6

Both my and your solution is not bullet proof if you need to check exactly against 3 or more tags.

WHERE tag_id IN ('tag1', 'tag2', 'tag3')

will return all clients that matches any two of the tags.

7

actually, OR ~will~ work, it is exactly what you get when you say IN ('tag1','tag2')

1 Like
8

Thanks for the warning. I tested it after your warning once more and it doesn’t return clients who match any two of the tags, it returns only clients who have all three tags.

9

Thanks. You say it will work but my tests confirmed it doesn’t.

10

No. What fixed your query was adding GROUP BY client_id HAVING COUNT(*) = 2.

With that it doesn’t matter whether you use OR or IN. Both work exactly the same. Try it if you don’t believe me: replace IN with OR in your final query. You’ll see the results are the same.

1 Like
11

Ah, thanks for pointing that out. Looks my test was not a properly controlled one.

12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.