Menu
I have two tables called conversations and users they are bound with the pivot table conversation_user which contains a user_id and a conversation_id column.
| conversation_id | user_id |
|-----------------|---------|
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
I want to send the database some user ids and retrieve the conversation they are in together.
For example:
When I send 1 and 2 i must receive the conversation_id 1, when I send 1, 2 and 3 i must receive the conversation_id 2.
How may I write a such query?
568
Once the Enable Selection feature has been turned on, you can use the Selection Arrow to select specific sections of an Excel Pivot Table. When the pointer changes to a thick black arrow, click, to select the row labels for that field.
In the PivotTable, right-click the value field, and then click Show Values As. Note: In Excel for Mac, the Show Values As menu doesn't list all the same options as Excel for Windows, but they are available. Select More Options on the menu if you don't see the choice you want listed.
You can use GROUP BY and HAVING:
SELECT conversation_id
FROM tab -- subquery/temp table/whatever
GROUP BY conversation_id
HAVING SUM(user_id = 1) > 0
AND SUM(user_id = 2) > 0
AND SUM(user_id = 3) > 0
AND SUM(user_id NOT IN (1,2,3)) = 0
SqlFiddleDemo
For 1 and 2:
SELECT conversation_id
FROM tab
GROUP BY conversation_id
HAVING SUM(user_id = 1) > 0
AND SUM(user_id = 2) > 0
AND SUM(user_id NOT IN (1,2)) = 0
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
