MySQL: Can I do a left join and pull only one row from the join table?

by

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 the s1.ticket_id = s2.ticket_id condition: it emulates the GROUP BY ticket_id.

  • the condition s2.id > s1.id : it is the SQL for “I only want the last solution”, it emulates the MAX(). I assumed that in your model, the last means with the greatest id but you could use here a condition on the date. Note that s2.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))

More Related Contents:

  1. How to do the Recursive SELECT query in MySQL?
  2. JOIN queries vs multiple queries
  3. MySQL indexes – what are the best practices?
  4. Subqueries with EXISTS vs IN – MySQL
  5. mysql select from n last rows
  6. Difference in MySQL JOIN vs LEFT JOIN
  7. 60 million entries, select entries from a certain month. How to optimize database?
  8. Checking multiple columns for one value
  9. Mysql Improve Search Performance with wildcards (%%)
  10. Whats the fastest way to lookup big tables for points within radius MySQL (latitude longitude)
  11. mysql, ifnull vs coalesce, which is faster?
  12. Higher cardinality column first in an index when involving a range?
  13. MySQL “IN” queries terribly slow with subquery but fast with explicit values
  14. How to optimise MySQL queries based on EXPLAIN plan
  15. How to estimate SQL query timing?
  16. Deleting millions of rows in MySQL
  17. Using LIMIT within GROUP BY to get N results per group?
  18. Quick easy way to migrate SQLite3 to MySQL? [closed]
  19. How to change the CHARACTER SET (and COLLATION) throughout a database?
  20. How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE?
  21. Getting all parent rows in one SQL query
  22. How can I manipulate MySQL fulltext search relevance to make one field more ‘valuable’ than another?
  23. Creating functions in phpMyAdmin – Error: access denied you need the super privilege for this operation
  24. How to connect to MySQL from the command line
  25. How can I enable the federated engine in mysql after installation?
  26. MySQL and Check Constraints
  27. How to select non “unique” rows
  28. why this mysql event can’t get run?
  29. Is it unreasonable to assign a MySQL database to each user on my site?
  30. What are the benefits of only_full_group_by mode?

Leave a Comment