SQL join across 3 tables, with multiple WHERE clause matches

Question

I have three tables: user, user_tag and tag. The most basic elements of these are reproduced below.

Users are linked to tags using the intermediate user_tag table. Every user can have zero or more tags. I'm keen to find those users that have one or more matching tags.

user

   Column    |              Type              |            Modifiers
-------------+--------------------------------+---------------------------------
 id          | integer                        | not null
 name        | character varying(150)         | not null

user_tag

   Column   |              Type              | Modifiers
------------+--------------------------------+-----------
 id         | integer                        | not null
 user_id    | integer                        |
 tag_id     | integer                        |

tag

   Column    |              Type              |            Modifiers
-------------+--------------------------------+---------------------------------
 id          | integer                        | not null
 name        | character varying(64)          | not null

So, finding users that have a single tag is simple:

select u.id,u.name,g.name 
from user u 
join user_tag t on t.user_id = u.id 
join tag g on g.id = t.tag_id 
where g.name='TAGX';

My question is, how do I match two or more tags?

Doing something like the following doesn't work.

select u.id,u.name,g.name 
from user u 
join user_tag t on t.user_id = u.id 
join tag g on g.id = t.tag_id 
where (g.name='TAGX' and g.name='TAGY');

It feels like I need to do a second join to match the second tag...?

Answer 1

First thing, change your condition from:

where (g.name='TAGX' and g.name='TAGY')

to:

where (g.name='TAGX' OR g.name='TAGY')

or:

where g.name in ('TAGX', 'TAGY')

As you want the union of tags TAGX and TAGY

Right now your output should be something like this:

+----+--------+------+
| ID |  Name  | Tag  |
+----+--------+------+
|  1 | User 1 | TAGX |
|  1 | User 1 | TAGY |
|  3 | User 3 | TAGX |
|  4 | User 4 | TAGY |
+----+--------+------+

As you mentioned that you want only the users who have 2 or more tags, the users 3 and 4 are intruders in the result. To get ride of them you will have to:

• Remove the tag column from select statement
• Group users by id and name
• Count the number of tags each user has
• Create a condition to filter the users with less than 2 tags

Like this:

select u.id,u.name
from user u 
join user_tag t on t.user_id = u.id 
join tag g on g.id = t.tag_id 
where g.name in ('TAGX', 'TAGY')
group by u.id,u.name
having count(u.id) < 2; 

With that your output should be:

+----+--------+
| ID |  Name  |
+----+--------+
|  1 | User 1 |
+----+--------+

If you want to check if your condition is filtering correctly, you can make a visual validation by showing the count column and removing the HAVING clause. Like this:

select u.id,u.name, count(u.id)
from user u 
join user_tag t on t.user_id = u.id 
join tag g on g.id = t.tag_id 
where g.name in ('TAGX', 'TAGY')
group by u.id,u.name;

Wich should show you:

+----+--------+-------+
| ID |  Name  | count |
+----+--------+-------+
|  1 | User 1 |     2 |
|  3 | User 3 |     1 |
|  4 | User 4 |     1 |
+----+--------+-------+

Answer 2

If you want to find users that have any of the two tags then the answer by Tarik will do what you want, but if you want to find users that have both tags (and maybe others) then this query will do that:

select u.id, u.name
from user u 
join user_tag t on t.user_id = u.id 
join tag g on g.id = t.tag_id 
where g.name in ('TAGX', 'TAGY')
group by u.id, u.name
having count(distinct g.name) = 2; 

The query above would return users that at least have tags TAGX and TAGY, but could have more tags. If you want the users that have those two tags and none other then one solution is to do a correlated not exists query like this:

select u.id, u.name, g.name
from user u 
join user_tag t on t.user_id = u.id 
join tag g on g.id = t.tag_id 
where not exists (
    select 1 
    from user_tag join tag on user_tag.tag_id = tag.id
    where tag.name not in ('TAGX', 'TAGY') 
    and user_tag.user_id = u.id
)