I have a table with conversation ids which is a one-to-many relationship to another table that has conversation users. The conversation users table has the id for the conversation, a unique id and a userID. I can use this:
SELECT c_id FROM conversations INNER JOIN conversation_users on conversation_users.cu_convo_id = conversations.c_id
which gives me all the rows but I need to find a way to do something like an IN statement because I need to find the conversation id given two user IDs.
Any ideas? Am I approaching this the wrong way completely?
WORKING ANSWER: IN case anyone needs it, the following worked for me:
SELECT cu_convo_id FROM conversations
INNER JOIN conversation_users on
conversation_users.cu_convo_id = conversations.c_id
WHERE cu_user_id IN (31,42)
GROUP BY cu_convo_id
HAVING count(distinct cu_user_id) = 2;
if I have 5 IDs to find, i put them on line 4 and then put the number 5 on line 6
Simply add a WHERE to your SQL:
SELECT c_id FROM conversations
INNER JOIN conversation_users on
conversation_users.cu_convo_id = conversations.c_id
WHERE conversations.usedID IN (1,2);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments