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.