I'm trying to do multiple levels of conditions for sorting by using a subquery, and I think there is something that is not correct in the subquery. What this is attempting to do is to take the top 8 pNumSales, then list anything where the checkbox in pCustom8 is checked, and then the rest of the pNumSales. The purpose is to be able to give a bump to selected items (new items, or other featured items) where there isnt enough sales data:

$customsortby='CASE WHEN pNumSales IN (SELECT pNumSales FROM products ORDER BY pNumSales DESC LIMIT 8) THEN 0 WHEN pCustom8 = TRUE THEN 1 ELSE 2 END, pNumSales DESC, pPopularity DESC'

The access to the SQL query via the new $customsortby works well for other advanced examples. This one puts anything with the pCustom8 checked at the very top, then it takes the new items within the last 90 days, and then puts them in order of pNumSales. This one works perfectly!

$customsortby='CASE WHEN pCustom8 = TRUE THEN 0 WHEN pDateAdded >= CURRENT_DATE - INTERVAL 90 DAY THEN 1 ELSE 2 END, pDateAdded DESC, pNumSales DESC'

In the first example, where I am trying to use a subquery, I think it is something in this part: WHEN pNumSales IN (SELECT pNumSales FROM products ORDER BY pNumSales DESC LIMIT 8) Anyone who has more knowledge of how this query is generated might be able to help. Thanks!

Hi Atlend
Are you getting an error with this query? Could you let us know what the error is?

Vince

Click Here for Shopping Cart Software
Click Here to sign up for our newsletter
Click Here for the latest updater

Not sure how to get it to print the error in this instance. What I get on the first example is that the page just doesn't load. On the version that is not using a subquery, it loads and functions correctly.

$customsortby='CASE WHEN pNumSales IN (SELECT pNumSales FROM products ORDER BY pNumSales DESC LIMIT 8) THEN 0 WHEN pCustom8 = TRUE THEN 1 ELSE 2 END, pNumSales DESC, pPopularity DESC'

I'm just not sure if the syntax is correct for the bold part. I looked into the code, but I am not really quite knowledgeable enough to know if my SELECT in the sub query is actually the correct way to do it in the context of the larger query. I think it can be a very powerful feature if I can understand it a little better. Thanks for any help you might be able to offer!

Hi Atlend
I had a quick look in Google and it seems you can't use a LIMIT in a subquery. But there are lots of suggestions as to alternatives if you just plug this into Google...

mysql limit in subquery

Vince

Click Here for Shopping Cart Software
Click Here to sign up for our newsletter
Click Here for the latest updater

Thanks, that tip is helpful! I found that many people were saying that I could wrap the subquery like this:

[code](select * from (SELECT pNumSales FROM products ORDER BY pNumSales DESC LIMIT 8) as SUBQUERY)[/code]

One more question if you happen to know. In a subquery situation, would I be SELECT for pNumSales or pID...or am I not even close?

[code]$customsortby_04='CASE pNumSales IN (select * from (SELECT pID FROM products ORDER BY pNumSales DESC LIMIT 8) as SUBQUERY) THEN 0 WHEN pCustom8 = TRUE THEN 1 ELSE 2 END, pNumSales DESC, pPopularity DESC';[/code]

OR

[code]$customsortby_04='CASE pNumSales IN (select * from (SELECT pNumSales FROM products ORDER BY pNumSales DESC LIMIT 8) as SUBQUERY) THEN 0 WHEN pCustom8 = TRUE THEN 1 ELSE 2 END, pNumSales DESC, pPopularity DESC';[/code]

Thanks for your help!!

Edit:
I am guessing that part of my issue is maybe the scope? I am obviously wanting to subquery through the existing results, but perhaps the way this new subquery is written it is taking a query of all of the records in the products table, not just the ones that are already selected.