Mehdi Bagheri Mehdi Bagheri - 1 month ago 4
MySQL Question

selecting unique rows from one table according to another table and then sorting it

I have two tables:

communications:
--------------------------------------------------
id | subject | timestamp | starter
--------------------------------------------------

messagepartners
-------------------------------------------------
id | commid | userid | type
-------------------------------------------------


the messagepartners table stores the ids of users who are participating in a conversation (stored in communications table).
the column commid in messagepartners stores the corresponding communications id (column id in communications)

for a specific user with the id of userid, I want to get a list of communications in which userid is participating. the list should contain no duplicates and be sorted by timestamp.

how should I write a sql query for this?
Thanks

Answer

Take it in steps

Here is a way to join the tables

SELECT *
FROM communications as c
JOIN messagepartners as p on c.id = p.commid

Filter by userid

SELECT *
FROM communications as c
JOIN messagepartners as p on c.id = p.commid
WHERE p.userid = X

Data we want

SELECT c.id, c.subject, c.timestamp
FROM communications as c
JOIN messagepartners as p on c.id = p.commid
WHERE p.userid = X

no dups

SELECT DISTINCT c.id, c.subject, c.timestamp
FROM communications as c
JOIN messagepartners as p on c.id = p.commid
WHERE p.userid = X

I suggest running each of these steps and understanding why you get the results you do at each step.