1

I have a parent table, foo, and a child table, bar, in a mySQL database.

Each row in foo may have zero or more child records in bar.

I want to get exactly 15 rows from the parent table, and all their child records. I initially did:

SELECT <columns> 
FROM foo 
LEFT JOIN bar 
    ON foo.a = bar.a 
WHERE <condition> 
ORDER BY <condition> 
LIMIT 15;

However, this will give me 15 joined rows. It's possible that the last row retrieved is a foo with multiple bar's, and that this query doesn't get all of the bar's. My requirement is that if a foo is in the result set, all of the bar's associated with that foo have to be returned as well.

What's the most efficient way to write a query that will get me 15 foo's with all of their bar's? I can think of a couple of ways of doing this, but none of them seem to be terribly elegant. (Doing a select on the parent table and then doing a second query with a join using WHERE <foo.id> IN ..., for example.) I'm not a DBA so I figured I'd ask the pros here.

1 Answer 1

1

Here's one way to do what you describe:

SELECT <columns>
FROM (
    SELECT <columns> 
    FROM foo
    WHERE <condition>     
    ORDER BY <expression-list-1> 
    LIMIT 15
) AS T 
LEFT JOIN bar 
    ON T.a = bar.a 
ORDER BY <expression-list-2> ;

You basically get 15 rows from foo according to some criteria and join the result with bar.

The first, internal ORDER BY list affects which 15 rows will be selected. The second, final list affects only the presentation ordering of the results.

The two ORDER BY lists do not need to be identical, although in practice they'll often be the same or the second an extension of the first, to resolve ties (how the children of the same parent are ordered).

9
  • This query will not work if there is WHERE condition on child table. May 11, 2021 at 5:52
  • @JigneshM.Khatri, can you elaborate on what you think is the problem?
    – Lennart
    May 11, 2021 at 8:41
  • Assume that there are 20 records in foo (parent) table and I want to select 15 records from that. Also there is WHERE condition on bar (child) table which filter out the rows. Now with the above query, I will not get 15 records every time, some of those will be missed as they will be filtered out in outer WHERE condition on bar table. May 11, 2021 at 8:57
  • As per my above comment, though there may be still 5 records available in foo (parent) table, which can be joined with bar (child) table, which can satisfy the outer WHERE condition and make up the 15 records total (my limit size), but those records will never be returned at all, as there is LIMIT in inner query. May 11, 2021 at 9:01
  • It's a matter of definition, if you want 15 rows from foo, with information from bar discarded, you can put the where predicate in the on clause of the left join. If you want to filter out those rows it's easiest to do with an exists clause
    – Lennart
    May 11, 2021 at 10:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.