CODEWITHSUNDEEP
mysqlsqlleft-joinlimit
mseifert
mseifert

Reputation: 5670

Limit a Left Join on the first table

I have two tables: gems and gemdetail that are left joined. I am trying to limit the LEFT JOIN to 10 records in the gems table. There are 2 other tables joined (gemreply and users) as well, but they do not contribute to the problem. The following does not work:

SELECT
    gems.gemid,
    gems.title,
    r.tot,
    gemdetail.filename
FROM
    (
        (
            gems
        LEFT JOIN(
            SELECT
                gemid,
                COUNT(*) AS tot
            FROM
                gemreply
            GROUP BY
                gemid
        ) AS r
    ON
        gems.gemid = r.gemid
        )
    LEFT JOIN gemdetail ON gems.gemid = gemdetail.gemid
    )
LEFT JOIN users ON gems.userid = users.userid
WHERE
    gems.grade = '7'
ORDER BY
    gems.gemid
LIMIT 0, 10;

This limits the total number of returned rows to 10, but since there are multiple detail records for each gem, I am left with fewer than 10 gem records. I've read every "LIMIT" post, but haven't found this situation.

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'.

Upvotes: 10

Views: 16388

Answers (4)

UTPAL BHUNIA
UTPAL BHUNIA

Reputation: 1

WITH last_orders AS (
     SELECT *
     FROM orders
     WHERE id IN (
        SELECT MAX(id)
        FROM orders
        GROUP BY customer_id
)
)
SELECT customers.id, customers.first_name, customers.last_name,
  last_orders.order_date, last_orders.order_status
FROM customers
JOIN last_orders
ON customers.id = last_orders.customer_id
ORDER BY customer_id;

Upvotes: 0

mseifert
mseifert

Reputation: 5670

Ten years later, I can LIMIT records within a nested query on the left table of a one to many JOIN where more than one record will be returned for the left table--even when there is a WHERE or ORDER BY clause on fields on the right side tables of the join.

  • In the inner query, have all the tables with joins necessary to satisfy the WHERE clause. Apply the limit there.
  • In the outer query, rejoin to all the same tables to add the many to many fields
SELECT
    alias.field_from_A,
    alias.field_from_B
FROM (
    SELECT DISTINCT
     /* ONLY fields from LEFT table (or tables
        with a one to one relationship with it) will
        allow LIMIT to apply only to the LEFT table. DISTINCT is needed due to JOIN*/
      A.field_from_A
    FROM
      A
      /* JOIN to your hearts content on tables
         which need to satisfy WHERE and ORDER BY */
      LEFT JOIN B ON A.field_from_A = B.field_from_B
      WHERE A.field_from_A = 'value' AND B.field_from_B = 'value 2'
      ORDER BY A.field_from_A, B.field_from_B
      LIMIT 0, 10
    ) alias
/* JOIN the one to many tables here to add the field info */
LEFT JOIN B ON A.field_from_A = B.field_from_B
/* the WHERE and ORDER BY need to be applied again */
WHERE alias.field_from_A = 'value' AND B.field_from_B = 'value 2'
ORDER BY alias.field_from_A, B.field_from_B

Upvotes: 0

Rachcha
Rachcha

Reputation: 8816

Try this:

SELECT g.gemid, g.title, r.tot, gemdetail.filename
FROM (SELECT * FROM gems WHERE grade = '7' LIMIT 10) g
LEFT JOIN (SELECT gemid, COUNT(*) AS tot FROM gemreply GROUP BY gemid) r
          ON r.gemid = g.gemid
LEFT JOIN gemdetail ON g.gemid = gemdetail.gemid
LEFT JOIN users ON g.userid = users.userid
ORDER BY g.gemid;

This should work.

Upvotes: 8

user1151835
user1151835

Reputation:

Something like that

   SELECT * FROM A
      INNER JOIN ( SELECT * FROM A WHERE A.FIELD1='X' ORDER BY A.FIELD2 LIMIT 10) X
             ON (A.KEYFIELD=X.KEYFIELD)
      LEFT JOIN B ON (A.FIELD = B.FIELD)
      LEFT JOIN C ON (A.FIELD = C.FIELD)

Upvotes: 10

Related Questions