Skip to content
Advertisement

SQL select only one row from second table JOIN

I have the following tables

Channel | id | name | descr |

channel_user

| channel_id | user_id |

channel_text

| channel_id | text_id |

text

| id | text | time

I 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;
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement