LIMIT with a JOIN

Missing some logic

[henry0]

$query = "select sl.li, sl.state_url, l.state_lost
FROM
states_lost sl, lost l
WHERE
l.state_lost = sl.state
ORDER BY sl.url_id ASC limit 24,13 "; //echo"$query";

Here is the problem:
Table sl has 51 rows
Table l has for that example only one row (in production it could be 1 to many rows)
The “limit clause” does not query from sl the first 24 rows but query the following 13 rows. (as per above example)

If a remove the limit it works fine, with limit clause on it returns 0
Which is not expected since at least one row meets the conditions

But I suspect that my condition does also apply to the l table that has only 1 row thus resulting in 0
How could I apply a “no limit” to l
And a limit as shown here on sl

Why I made it:
I have a result page showing four <div> (col) each echoing the result of one of 4 queries with each a different limit clause in order to split the 51 rows
First <div >0 - 12
Second <div> 12 -12
Third <div> 24 - 13
Fourth <div> 37 – 14

I could fix it be creating 4 tables and rem the limit clause but it will be like cheating!

[FalseDawn]

I'd try either a LEFT OUTER JOIN from l to sl (that would give you the "no limit" you're after, I think)

An alternative solution might involve copying the results to a temp table first, then applying the LIMIT clause on that.

[edited by: FalseDawn at 12:23 am (utc) on June 12, 2007]

[henry0]

Thanks
I'll try the left outer join.

Yes, the tmp table is doable however more on the procedural side, which I try to avoid.
But if I cannot do otherwise I could use it, a tmp table will be less processing, I believe, than creating four more tables to “Hard split” my rows.