SQL query with row limits from one table and not a set of results

Question

I run a simple query with a connection similar to

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

Since t1 has many lines in t2 (any number above 2), the LIMIT operator does not return the first 5 lines from t1 and the corresponding entries from t2 , but 5 lines, which usually include 2-3 lines 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

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

Please note that if you use the restriction without the "order by" clause, it is not determined which 5 lines you will receive. Consider adding an "order by" clause if that is not what you want.

Answer 2

This is a classic pagination request. I suggest splitting it into two queries:

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

Take these identifiers and put them in the following query:

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

Answer 3

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 4

You can group it by a unique column in t1:

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

But do you need table t2 in a specific order?