CODEWITHSUNDEEP
mysqlsql
Wes
Wes

Reputation: 419

MySQL check if comma separated values match rows in table

I'm trying to figure out how I can query my table to see if a group of user_id's match a conversation_id.

Query 1 should return result for: user_id 1 is looking to see if there are any conversation_id's with just user_id = 2 and user_id = 1 in it. (Should return a row for each conversation_id = 1, 2, 4, 5 based on SQL Fiddle example) 

conversation_id
1
2
4
5

Query 2 should return result for: user_id 1 is looking to see if there are any conversation_id's with user_id = 2, user_id = 1, and user_id = 4 in it. (Should return 0 rows as it doesn't exist in the SQL Fiddle example)

The table setup is located at SQL Fiddle

Upvotes: 0

Views: 376

Answers (2)

jpw
jpw

Reputation: 44871

You can use a combination of group by ... having and a correlated exists subquery to achieve the result you want:

-- Query 1:

SELECT 
  conversation_id
FROM 
  users_conversations uc
where not exists (
  select 1 from users_conversations
  where conversation_id = uc.conversation_id
  and user_id not in (1,2)
)
group by conversation_id
having count(distinct user_id) = 2;

-- Query 2: same query, only different numbers.

SELECT 
  conversation_id
FROM 
  users_conversations uc
where not exists (
  select 1 from users_conversations
  where conversation_id = uc.conversation_id
  and user_id not in (1,2,4))
group by conversation_id
having count(distinct user_id) = 3;

Sample SQL Fiddle

Note that the first query will not return 1,2,4,5 but rather 2,5 but in your sample data neither 1 or 4 has only user_id 1 and 2 as participants (conversation 1 has 1,2,3,4, and conversation 4 has 1,2,5).

Upvotes: 1

Kamil Bogumił
Kamil Bogumił

Reputation: 121

If i understand it right it should be something like his.

Q1:

SELECT
CASE 
WHEN
count(distinct CASE WHEN user_id in ('1','2') THEN user_id END)>='2' 
THEN `conversation_id` 
END 'test'
FROM 
users_conversations
where 1
group by `conversation_id`

Q2:

SELECT
CASE 
WHEN 
count(distinct CASE WHEN user_id in ('1','2','4') THEN user_id END)>='3' 
THEN `conversation_id` 
END 'test'
FROM 
users_conversations
where 1
group by `conversation_id`

http://sqlfiddle.com/#!9/fb29d/9

Upvotes: 1

Related Questions