Left join with multiple rows from right table in mySQL

Asked Sep 09 '17 at 00:49

Active Sep 09 '17 at 15:54

Viewed 19 times

I have two tables (agents, visits).

Agents can have many visits each with its timestamp

I want to select all agents using left join without agent duplicates and if the agent has many visits then show the most recent one i.e select the last timestamp in visits table.

I tried the following query

SELECT * 
  FROM agents 
  LEFT 
  JOIN  visits 
    ON agent_id = visit_id 
 GROUP 
    BY agent_id

This works well without agent duplicates but shows the oldest visit and I want to show the last one (the most recent timestamp in the second table visits).

edited Sep 09 '17 at 15:54

Jonathan Leffler

730,956
141
904
1,278

asked Sep 09 '17 at 00:49

Emad Saeed

1

you have to use sub query for right table. – Md. Shahadat Sarker Sep 09 '17 at 03:20
'This' does not 'work well' under any circumstances! – Strawberry Sep 09 '17 at 05:19
@Strawberry please give yourself 10 seconds to understand the question before marking it as a duplicate. It's not even a similar question – Emad Saeed Sep 09 '17 at 10:44
1

See [Why should I provide an MCVE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Sep 09 '17 at 10:45

Left join with multiple rows from right table in mySQL

0 Answers0