I have the following tables
Channel | id | name | descr |channel_user
| channel_id | user_id |channel_text
| channel_id | text_id |text
| id | text | timeI am trying to retrieve all channels a user is subscribed to along with the last time a channel has received a text (by user). example: | id | name | descr | lastUpdate
So far I have the following
SELECT c.*,t.time as lastUpdate FROM chanel c INNER JOIN chanel_user cu ON c.id = cu.chanel_id INNER JOIN chanel_text ct ON cu.chanel_id=ct.chanel_id INNER JOIN text t ON t.id=ct.text_id JOIN ( SELECT id,MAX(time) maxtime FROM text GROUP BY id ) b ON t.time = b.maxtime JOIN users u ON cu.user_id=u.id WHERE u.id=2 ORDER BY c.id;
Advertisement
Answer
If I understand correctly, you need the JOIN
between channel_text
and text
in the subquery. Also there is no u
table in your query, but it is not needed:
SELECT c.*, t.time as lastUpdate FROM chanel c INNER JOIN chanel_user cu ON t.id = ct.text_id JOIN (SELECT ct.chanel_id, MAX(time) as maxtime FROM chanel_text ct JOIN text t ON ct.text_id = t.id GROUP BY ct.chanel_id ) ct ON cu.chanel_id = ct.chanel_id WHERE cu.id = 2 ORDER BY c.id;