Limit a Left Join on the first table

Question

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'.

Humerus

Ok, so do you want the record of first 10 gems and all of their details and then join or LEFT-join it with gemreply and users? – Paige

What version of MySQL? If you have constraints can you please post them in the question (and add the MySQL tag)? If you don't tell people everything then how can you expect a reasonable answer to your question? – Dollarfish

This query is bound to fail as a subquery is expected inside those brackets in the FROM clause. Instead, it only lists the tables. – Paige

minimal reproducible example Please clarify via edits, not comments. – Sam

Answer 1

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)

Decorator

Answer 2

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.

Paige

Answer 3

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

Humerus

LEFT JOIN returns INNER JOIN rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. After a LEFT JOIN a WHERE, INNER JOIN or HAVING that requires a right [sic] table column to be not NULL removes any rows with introduced NULLs, ie leaves only INNER JOIN rows, ie "turns OUTER JOIN into INNER JOIN". You have that. – Sam

@Sam Thanks for pointing out the extra commas. I'm not sure what the problem with two left joins in a row is. The first LEFT JOIN is on the inner query and the second is on the outer. They accomplish different things - the first applies the condition as part of the LIMIT, the second is to include the joined fields in the result set. Regarding LEFT JOINS being turned into INNER joins, in my case the WHERE clause is dynamic - it doesn't always have a condition on the right table column. So, sometimes (most of the time) it is a LEFT join. – Humerus

Look at the second/outer FROM in the query, you have literally written "LEFT JOIN LEFT JOIN". minimal reproducible example My point re all your left joins on this page was that the queries would return the same result if they were inner joins & that since you wrote left join instead you may wrongly think otherwise. Typically when people do this it's part of their bug/misconceptions. So it would be helpful if the post mentioned that you were doing it on purpose. You do it again in your preceding question from 4 years after this one on which I also commented today. (Please clarify via edits, not comments.) – Sam

Answer 4

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;

Corbin

Code-only answers are poor, please explain how/why this addresses the problem. How to Answer How to Ask – Sam