MySQL: Can I do a left join and pull only one row from the join table?
Let me sum up what I understood: you'd like to select each ticket and its last solution.
I like using the following pattern for this kind of question as it avoids the subquery pattern and is therefore rather good where performance is needed. The drawback is that it is a bit tricky to understand:
SELECT
t.*,
s1.*
FROM tickets t
INNER JOIN solutions s1 ON t.id = s1.ticket_id
LEFT JOIN solutions s2 ON s1.ticket_id = s2.ticket_id AND s2.id > s1.id
WHERE s2.id IS NULL;
I wrote only the heart of the pattern for a better understanding.
The keys are:
the LEFT JOIN of the
solutions
table with itself with thes1.ticket_id = s2.ticket_id
condition: it emulates theGROUP BY ticket_id
.the condition
s2.id > s1.id
: it is the SQL for "I only want the last solution", it emulates theMAX()
. I assumed that in your model,the last
meanswith the greatest id
but you could use here a condition on the date. Note thats2.id < s1.id
would give you the first solution.the WHERE clause
s2.id IS NULL
: the weirdest one but absolutely necessary... keeps only the records you want.
Have a try and let me know :)
Edit 1: I just realised that the second point assumption was oversimplifying the problem. That makes it even more interesting :p I'm trying to see how this pattern may work with your date, id
ordering.
Edit 2: Ok, it works great with a little twist. The condition on the LEFT JOIN becomes:
LEFT JOIN solutions s2 ON s1.ticket_id = s2.ticket_id
AND (s2.date > s1.date OR (s2.date = s1.date AND s2.id > s1.id))
When you have a inline view in the SELECT clause it must perform that select for every single row. I find its better in cases like this to put an inline view in the FROM clause instead which will perform the select once.
SELECT t.*,
Concat_ws(" * ", Date_format(s.date, "%c/%e/%y"), s.hours_spent,
Concat_ws(":", s.agent_id, s.body))
FROM tickets t
INNER JOIN (SELECT solutions.ticket_id,
Max(solutions.date) maxdate
FROM solutions
GROUP BY solutions.ticket_id) last_solutions
ON t.id = last_solutions.ticket_id
INNER JOIN (SELECT solutions.ticket_id,
solutions.date,
Max(solutions.id) maxid
FROM solutions
GROUP BY solutions.ticket_id,
solutions.date) last_solution
ON last_solutions.ticket_id = last_solution.ticket_id
and last_solutions.maxDate = last_solution.Date
INNER JOIN solutions s
ON last_solution.maxid = s.id
WHERE t.date_closed IS NULL
OR t.date_closed >= '2012-06-20 00:00:00'
ORDER BY t.id DESC
Note: You may need to make it a LEFT join depending on your needs