SQL query with limit on rows from one table, not the result set

Question

I'm running a simple query with a join, similar to

SELECT t1.a, t2.b FROM t1 LEFT JOIN t2 ON ... LIMIT 5

As t1 has-many rows in t2 ( any number above 2 ) the LIMIT statement does not return the first 5 rows from t1 and corresponding entries from t2, but 5 rows which usually include 2-3 rows from t1.

How can I write this query to get the first 5 rows from t1 and the corresponding entries from t2?

---

Using MySQL 5.0.45.

Answer 1

I believe the following will do the trick:

SELECT t1.a, (SELECT t2.b FROM t2 WHERE t2... = t1...) AS b FROM t1 LIMIT 5

Answer 2

SELECT t3.a, t2.b FROM (SELECT * FROM t1 LIMIT 5) t3
LEFT JOIN t2 ON ...

Note that if you use limit without an 'order by' clause, it is not defined which 5 rows you will get. Consider adding an 'order by' clause if this is not what you want.

Answer 3

This is a classic pagination query. I suggest breaking it down into two queries:

SELECT DISTINCT t1.id FROM t1 LEFT JOIN t2 ON ... LIMIT 5

Take these id's and place them in the following query:

SELECT t1.a, t2.b FROM t1 LEFT JOIN t2 ON ... WHERE t1.id IN (?,?,?,?,?) 

Answer 4

You can group it by the unique column in t1:

SELECT * FROM t1 JOIN t2 ON ... GROUP BY t1.id LIMIT 5

But do you need the t2 table to be in a specific order?