Re: LIMIT in a subselect, but without using subselects
- From: Bill Karwin <bill@xxxxxxxxxx>
- Date: Mon, 18 Jul 2005 14:36:21 -0700
MrKrinkle wrote:
select * from shoppingcarts left join cartitems on cartitems.cartuid= shoppingcarts.uid where shoppingcarts.uid in (select uid from shoppingcarts order by created desc LIMIT 50,10);
How can I do that with a left join instead? thanks
I would do this with two separate queries:
1. select uid from shoppingcarts order by created desc LIMIT 50,10
2. Build a string that is the comma-separated list of uid's from the result of the first query, and interpolate it into the second query:
select * from shoppingcarts left join cartitems
on cartitems.cartuid = shoppingcarts.uid
where shoppingcarts.uid in ( $LIST_OF_UIDS )Be careful to test if the list is empty, because "IN ( )" is a syntax error in SQL.
There might be a clever way to use outer joins to do this in a single query (along the lines of Nis' solution to the thread "Limit the number of a left join" a couple of weeks ago on this newsgroup). But the extra work it takes to code, test, and maintain such clever queries is usually too great to justify the slight benefit from doing the query in one step.
Sometimes it is more straightforward to just do a query in two steps, and move on to the next problem in your project. Also, keep in mind that someday, someone with only modest skill in SQL might have to maintain your code, and using very sophisticated queries can make it harder for that person to understand it.
Regards, Bill K. .
- References:
- LIMIT in a subselect, but without using subselects
- From: MrKrinkle
- LIMIT in a subselect, but without using subselects
- Prev by Date: Dont miss Extreme Markup Languages 2005 -- Two Weeks Away
- Next by Date: Re: Dont miss Extreme Markup Languages 2005 -- Two Weeks Away
- Previous by thread: LIMIT in a subselect, but without using subselects
- Next by thread: Re: LIMIT in a subselect, but without using subselects
- Index(es):
Relevant Pages
|
