jump to navigation

Using limit in mysql join December 18, 2009

Posted by maxmil in : MySql , trackback

Just found myself in the scenario of needing to join two tables which have a 1 – n relationship. In the second table i only wanted to return the first two results.

This is one way to do it.

FROM foo f1
LEFT JOIN foo f2 ON f2.col2 = f1.col2 AND f2.col1 < f1.col1 GROUP BY f1.col1, f1.col2 HAVING COUNT(f2.col1) <= 1 ORDER BY f1.col2, f1.col2;

What you are doing here is joining the second table with itself. This will give you exactly one result with the first row in the second table, two for the second row, three for the third... etc.

You then group them and use the HAVING clause to get the row that you want.

This is not very efficient, but if your dataset is small or it's a one off query it does the job.


no comments yet - be the first?