Most efficient way to LIMIT results in a JOIN?
- by johnnietheblack
I have a fairly simple one-to-many type join in a MySQL query. In this case, I'd like to LIMIT my results by the left table.
For example, let's say I have an accounts table and a comments table, and I'd like to pull 100 rows from accounts and all the associated comments rows for each.
Thy only way I can think to do this is with a sub-select in in the FROM clause instead of simply selecting FROM accounts. Here is my current idea:
SELECT a.*, c.* FROM
(SELECT * FROM accounts LIMIT 100) a
LEFT JOIN `comments` c on c.account_id = a.id
ORDER BY a.id
However, whenever I need to do a sub-select of some sort, my intermediate level SQL knowledge feels like it's doing something wrong.
Is there a more efficient, or faster, way to do this, or is this pretty good?
By the way...
This might be the absolute simplest way to do this, which I'm okay with as an answer. I'm simply trying to figure out if there IS another way to do this that could potentially compete with the above statement in terms of speed.
