Select where join match list of value

Question

I'm not sure how to ask this question. I have the following schema :

message_id	message_content
1	Hello World
2	EHLO

message_id	concerned_user
1	laura
1	vick
1	john
2	laura
2	vick

How to select message_id which concern laura and vick (and only laura and vick). Expected result is ̀[2]`.

I'm sure it is basic SQL but I don't find it.

As questionned in some answer: I use PostgreSQL.

Thanks !

Thorsten Kettner · Answer 1 · 2022-01-09T19:19:09.737

2

Build a string of the concerned users and see if that matches what you are looking for. In PostgreSQL the string concatenation group function is STRING_AGG:

select message_id
from mytable
group by message_id
having string_agg(concerned_user, ',' order by concerned_user) = 'laura,vick';

If there can be duplicates in the table (two or more rows for the same message_id and concerned_user), you must add DISTINCT: string_agg(distinct concerned_user ...).

edited Jan 09 '22 at 19:19

answered Jan 09 '22 at 19:13

Thorsten Kettner

89,309
7
49
73

Hello, thanks ! I forgotted my DB. I Use PGSQL. Thanks for the concat idea. – bux Jan 09 '22 at 19:16
Okay, I've updated the answer to match PostgreSQL syntax. – Thorsten Kettner Jan 09 '22 at 19:19

LukStorms · Answer 2 · 2022-01-09T19:33:29.470

1

If only 2 specific users have the same message, then that message will have only 2 unique users.
And a count for each user won't be zero.

SELECT message_id
FROM your_table
GROUP BY message_id
HAVING COUNT(DISTINCT concerned_user) = 2
AND COUNT(CASE WHEN concerned_user = 'laura' THEN 1 END) > 0
AND COUNT(CASE WHEN concerned_user = 'vick'  THEN 1 END) > 0;

db<>fiddle here

edited Jan 09 '22 at 19:33

answered Jan 09 '22 at 19:15

LukStorms

28,916
5
31
45

This gets you both message 1 and 2, because both contain laura and vick. The OP wants only message #2, because only this concerns laura and vick and no others. – Thorsten Kettner Jan 09 '22 at 19:21
1

@ThorstenKettner Thx for the notice. Fixed now. – LukStorms Jan 09 '22 at 19:35

score 0 · Answer 3 · answered Jan 09 '22 at 20:38

In very basic SQL it can be done with a statement doing something using WHERE EXISTS / NOT EXISTS like (in pseudo code, real SQL statement follows):

select messages-from-laura  
  where exists(message-from-vick-with-same-id) 
   and not exist(message-from-someone-else with same-id)

same-id refering to laura-message id.

This is standard SQL, it works in postgresql, mssql (tested with both) and probably with others.

select *
from messages laura_messages
where concerned_user = 'laura'
and (exists (select 1 from messages vick_messages 
            where vick_messages.message_id = laura_messages.message_id))
and not exists (select 1 from messages other_messages 
            where other_messages.message_id = laura_messages.message_id
                      and other_messages.concerned_user not in ('laura','vick'))

Note the use of aliases for the main table and subqueries tables (in SQL, you can add an alias name after the table name in the FROM TableName clause) and the reference of the first message id in EXISTS subqueries.

Note also that you don't need to return something in subqueries, just that some data exists, so doing SELECT 1 is fine.

And you could write the equivalent query with joins but the optimizer is very good at rewriting queries so it would probably be the same, this one is simpler imho. Or you could also use GROUP or something more sophisticated, the nice thing with sql is that you often have several possibilities to write the same query :)

Select where join match list of value

3 Answers3