jump to navigation

Using limit in mysql join December 18, 2009

Posted by maxmil in : MySql , add a comment

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.


SELECT
f1.col1,
f1.col2
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.