"limit" clause on joining?

Question

table1:

id , otherColumn, otherColumn2 ...

table2:

id, table1_id, someOtherColumns...

I.E. one to many relationships

A trivial join would be:

select * from table1 left outer join table2 on table2.table1_id=table1.id;

I want to do something different: for each row in table1, bring at most 1 row from table2, no matter which if there are several candidates. Just as I can limit the amount of results on a regular select

Is this possible? How?

You've been here for twelve months. Format your posts please. — Lightness Races in Orbit, Sep 05 '11 at 18:03
So, @Tomalak, do you have something to contribute to the *topic* of the question? — shealtiel, Sep 05 '11 at 18:08
possible duplicate of [mysql: Using LIMIT within GROUP BY to get N results per group?](http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group) or http://stackoverflow.com/q/3869571/27535 This is a tricky in MySQL as it doesn't have ROW_NUMBER or CROSS APPLY — gbn, Sep 05 '11 at 20:11
It's only a half-duplicate (although that is a crucial part of the puzzle) because this is in relationship to a join as well. — , Sep 05 '11 at 21:01
You can try modifying this: http://stackoverflow.com/questions/7233757/order-within-group-by/7234200#7234200 — ypercubeᵀᴹ, Sep 05 '11 at 23:03

score 1 · Accepted Answer · 2011-09-05T18:20:10.227

Possibilities:

Use a sub-select. This will force the inner result-set to be limited. Advantage is that grouping/aggregation operations can be used. (I am not sure what issues, if any, MySQL has with sub-selects and holistic query planning.)
Use a WHERE in the primary statement and let the SQL engine "do it's thing". If the WHERE can be "moved" before the join, a smart engine will do so as it will result in less rows being processed. I think this is part of the basic Relational Algebra model (for independent records), but I am not sure. Look at the query plan. (I do not use MySQL, so I do know what optimizations are done.)

And, as always, verify results and run performance tests if it matters.

Happy coding.

"limit" clause on joining?

1 Answers1