Mysql – Get a specific number of rows from parent table

join;MySQL

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.

Best Answer

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

Related Question