Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
04-12-2009, 08:53 PM #1
- Join Date
- Feb 2008
- Thanked 0 Times in 0 Posts
need some help with the following statement. i'm trying to use it to detect from my database if there is more than ONE row where sku='$sku' AND inStock='yes' ie.
ROW SKU inStock
1 1234 yes
2 1234 yes
3 1234 no
In this case it will return rows 1 & 2, but not 3 as it is listed as not in stock.
SELECT sku,inStock,COUNT(*) as total FROM products WHERE sku='$sku' AND inStock='yes' GROUP BY sku,inStock HAVING total > 1 ORDER BY search, save
04-13-2009, 03:47 PM #2
- Join Date
- Dec 2005
- Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
- Thanked 637 Times in 625 Posts
Just in case the HAVING clause doesn't let you use a column in the select, then you can use COUNT(*) and that will definitely work. And, I'm not really sure what the point of your ORDER BY is, that won't do anything for you.
04-13-2009, 10:50 PM #3
Excuse me, but what is the POINT in returning the inStock field when you *KNOW* that the value is going to be "yes", because that's what is in you WHERE clause???
For that matter, what's the point in returning the sku field? Again, you are only going to get one value there.
No need for any GROUP BY, because there *IS* only one group.Code:SELECT COUNT(*) as total FROM products WHERE sku='$sku' AND inStock='yes' HAVING COUNT(*) > 1
And as Fumigator said, your ORDER BY clause makes no sense. The only field you COULD do ORDER BY is the sku field, but since this query will return only ZERO or ONE records, there's no point in any ORDER BY, at all.
Also, my personal opinion is that you should get rid of the HAVING clause.
Reason: It's going to be easier to know that you will always have one record, and then use your PHP code to check the COUNT() value. Simpler than doing the EOF check, really.
So I would just do
And then you can just skip results of zero and one in your PHP code.Code:SELECT COUNT(*) as total FROM products WHERE sku='$sku' AND inStock='yes'