postgresql - SQL join across 3 tables, with multiple WHERE clause matches -

i have 3 tables: user, user_tag , tag. basic elements of these reproduced below.

users linked tags using intermediate user_tag table. every user can have 0 or more tags. i'm keen find users have 1 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 have single tag simple:

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

my question is, how match 2 or more tags?

doing following doesn't work.

select u.id,u.name,g.name  user u  join user_tag t on t.user_id = u.id  join tag g on g.id = t.tag_id  (g.name='tagx' , g.name='tagy'); 

it feels need second join match second tag...?

first thing, change condition from:

where (g.name='tagx' , g.name='tagy') 

to:

where (g.name='tagx' or g.name='tagy') 

or:

where g.name in ('tagx', 'tagy') 

as want union of tags tagx , tagy

right output should this:

+----+--------+------+ | id |  name  | tag  | +----+--------+------+ |  1 | user 1 | tagx | |  1 | user 1 | tagy | |  3 | user 3 | tagx | |  4 | user 4 | tagy | +----+--------+------+ 

as mentioned want users have 2 or more tags, users 3 , 4 intruders in result. ride of them have to:

• remove tag column select statement
• group users id , name
• count number of tags each user has
• create condition filter users less 2 tags

like this:

select u.id,u.name user u  join user_tag t on t.user_id = u.id  join tag g on g.id = t.tag_id  g.name in ('tagx', 'tagy') group u.id,u.name having count(u.id) < 2;  

with output should be:

+----+--------+ | id |  name  | +----+--------+ |  1 | user 1 | +----+--------+ 

if want check if condition filtering correctly, can make visual validation showing count column , removing having clause. this:

select u.id,u.name, count(u.id) user u  join user_tag t on t.user_id = u.id  join tag g on g.id = t.tag_id  g.name in ('tagx', 'tagy') group u.id,u.name; 

wich should show you:

+----+--------+-------+ | id |  name  | count | +----+--------+-------+ |  1 | user 1 |     2 | |  3 | user 3 |     1 | |  4 | user 4 |     1 | +----+--------+-------+