SQL limit SELECT but not JOIN

问题:

I'm implementing pagination on my BD. My problem is when I want limit the SELECT statement but not the JOIN. Example, a product can got many prices:

SELECT * FROM product
LEFT JOIN price ON product.id == price.id_product
LIMIT 20

But I want to get 20 products with each one with their prices. How I can limit the statement SELECT, but not LEFT JOIN.

Example:

product             price.id  price.id_pruct  price.price
   1                   1            1               50
   2                   2            1               30
   3                   3            1               40
                       4            1               20
                       5            2               30

SELECT * FROM product
LEFT JOIN price ON product.id == price.id_product
LIMIT 3

Return:

product  price.id id_prodcut price
1          1           1      50
1          2           1      30
1          3           1      40

But I Want

product  price.id id_prodcut price
1          1           1      50
1          2           1      30
1          3           1      40
1          4           1      20
2          5           2      30
3          .            .     .

Three products (limit 3)

Thanks. I hope you can help me.

回答1:

I would write a subquery to get the three first products (or whatever condition you choose) like this:

SELECT id
FROM product
ORDER BY id
LIMIT 3;

Once I have that, I can select everything from the price table as long as the id is in that subquery. You can do this using a join:

SELECT p.*
FROM price p
JOIN(
   SELECT id
   FROM product
   ORDER BY id
   LIMIT 3) tmp ON tmp.id = p.product_id;

Here is an SQL Fiddle example using your sample data, and I also added a row that won't be returned so you can see that it works.

回答2:

Modify your query to limit the number of product rows before joining it to the price table. This means we want to to join the results of a query to a table, or in other words, we write a query containing a subquery:

SELECT *
FROM (
    SELECT *
    FROM product
    ORDER BY id_product
    LIMIT 3
) p
LEFT JOIN price ON p.id = price.id_product

Hope that helps.