Comparing the values returned by two sub-queries

[mralston]

I've got a MySQL database, with two tables that we're interested in:

products
product_id (PK), category_id (FK), title, description, image, price, quantity, start_date, lifetime, renewal_date

orders
order_id (PK), product_id (FK), user_id (FK), order_date, eta, payment_method, status

products contains a listing of all the products on offer (they are second hand computers in this case). If a product listed has a quantity of more than 1 (1 being the default) then this means that we have more than one identical product available matching the entry in the products table.

orders contains all the orders placed by visitors to the website. Each procuct can have orders a maximum number of orders matching its quantity value.

If a product has a quantity of 6, then it will be listed on the website as for sale until it has 6 matching orders in the orders table. Once it has 6 orders it will should no longer appear in the query that lists all the available products. It's this query that is causing me grief. I've got:

SELECT

product_id AS prod_id, title AS product_title, description AS product_description, image, price, start_date, lifetime, renewal_date

FROM products
WHERE category_id=8
AND
(

(SELECT COUNT(*) FROM orders WHERE product_id=prod_id) < (SELECT quantity FROM products WHERE product_id=prod_id)

)

ORDER BY title, description;

MySQL seems to have trouble with the bit where I'm trying the compare the numerical values returned by the two sub-queries. It says:

[localhost] ERROR 1064: You have an error in your SQL syntax near 'SELECT COUNT(*) FROM orders WHERE product_id=prod_id) < (SELECT quantity FROM pr' at line 10

Is there some function I can use to get the numerical value returned by each sub-query before I ask it to do a less-than operation? Or is there a better way to do this?

[PilgrimPete]

Could you rewrite the subquery a bit like this?

SELECT 	product_id AS prod_id, 
	title AS product_title, 
	description AS product_description, 
	image, price, 
	start_date, 
	lifetime, 
	renewal_date
FROM 	products P
WHERE 	category_id = 8
AND 	((SELECT COUNT(*) NumOrders FROM orders WHERE (product_id = P.prod_id) AND (NumOrders < P.quantity)
ORDER BY title, 
	description;

[swatty]

Could you not add some fields to the order table.
quantity.
If i want to order 3 of them products where you have a quantity of 6.

I would if an order is placed decrease the value of quantity in the products table.

If an order is deleted then increase the value for this product in the quantity field.

Doing this you only have to check if the quantity for a product is higher than 0.

[mralston]

PilgrimPete,

I can see what you're getting at, and it makes sense to me but not aparrently, to MySQL. WIth your query it says

[localhost] ERROR 1064: You have an error in your SQL syntax near 'SELECT COUNT(*) NumOrders FROM orders WHERE product_id = P.prod_id) AND (NumOrde' at line 10

I tried rejigging it slightly too...

SELECT product_id AS prod_id, 
	title AS product_title, 
	description AS product_description, 
	image, price, 
	start_date, 
	lifetime, 
	renewal_date,
	(SELECT COUNT(*) FROM orders WHERE product_id = p.prod_id) AS NumOrders
FROM 	products AS p
WHERE category_id = 1
AND NumOrders < p.quantity
ORDER BY title, description;

but it says

[localhost] ERROR 1064: You have an error in your SQL syntax near 'SELECT COUNT(*) FROM orders WHERE product_id = p.prod_id) AS NumOrders
FROM pro' at line 8

[PilgrimPete]

First up, I appear to have a parentheses match-up error in my query; it should be:

AND 	(SELECT COUNT(*) NumOrders FROM orders WHERE (product_id = P.prod_id) AND (NumOrders < P.quantity))

but that doesn't sound like the issue. It seems more fundamental than that. I'll get thinking and let you know what I come up with.

[PilgrimPete]

How about some creative joining.
Will this work..?

SELECT 	product_id AS prod_id, 
	title AS product_title, 
	description AS product_description, 
	image, price, 
	start_date, 
	lifetime, 
	renewal_date
FROM 	products p, orders o
WHERE 	(category_id = 8)
AND	(o.product_id = p.prod_id) 
AND 	(COUNT(o.order_id) < P.quantity)
ORDER BY title, 
	description;

[mralston]

I realised there was a problem with your LISP...(Lots of Silly Irritating Parentheses ), but I resolved that myself. I think MySQL has a fundamental grudge about the way I'm throwing sub-queries at it willy nilly.

Well then...that next piece of SQL you've come out with...dunno about that...starting to get over my head. Looks like something my step-mum would come out with (she's an Oracle DBA for a university, pitty she's on holiday out of the country at the mo! And off went my database tech support along with her ). Anyways, I digress...I think I understand what that code you've given me does, I'm gonna give it a go now.

[mralston]

It was complaining about product_id being ambigous, so I changed it as follows (I don't really need the product_id field renamed to prod_id):

SELECT 	p.product_id, 
	title AS product_title, 
	description AS product_description, 
	image, price, 
	start_date, 
	lifetime, 
	renewal_date
FROM 	products p, orders o
WHERE 	(category_id = 8)
AND	(o.product_id = p.product_id) 
AND 	(COUNT(o.order_id) < P.quantity)
ORDER BY title, 
	description;

Now it's winging "Invalid use of group function", so my guess is it wants a GROUP BY clause (which I don't understand!). I've tried putting one in, but I honestly don't know what fields to specify in it and in what order.

[mralston]

Hmmm... this article explains why my original query didn't work.

[PilgrimPete]

Makes sense - also exposes my woeful lack of MySQL knowledge...

The GROUP BY error also makes sense: you might try returning to your COUNT(*) syntax, but I'm clutching at straws...

Temporary workaround solution:

Two queries:

DEFINITION OF vwOrderCount:
SELECT 	COUNT(order_id) AS OrderCount
FROM 	orders o
GROUP BY product_id;

DEFINITION OF vwTheRealThing:
SELECT 	p.product_id, 
	title AS product_title, 
	description AS product_description, 
	image, price, 
	start_date, 
	lifetime, 
	renewal_date
FROM 	products p 
INNER JOIN vwOrderCount o
ON 	o.product_id = p.product_id
WHERE 	(category_id = 8)
AND 	(o.OrderCount < p.quantity)
ORDER BY title, 
	description;

[mralston]

You can betcha life that MySQL doesn't support views.
Here goes nothing...

[mralston]

Invalid syntax near "DEFINITION"...

I can tell I'm going to have to re-think my strategy here...
I might try splitting it into two completely seperate queries and using a bit of PHP in between them...

[PilgrimPete]

Yeah, but before you do please note my inability to write SQL. Ooooops:

DEFINITION OF vwOrderCount:
SELECT 	COUNT(order_id) AS OrderCount, product_id
FROM 	orders o
GROUP BY product_id;

That's what comes of writing air code...

[PilgrimPete]

The DEFINITION bits were just my comments, that's why I put them in red. Sorry.
I don't know the MySQL syntax for creating views.

[mralston]

Hmm. As you might have come to expect, it still moans.

How about I do it like this instead (I'll put my WHERE clause in after)...

SELECT products.product_id, category_id, title, description, image, price, quantity, COUNT(order_id) AS order_count
FROM products LEFT OUTER JOIN orders ON products.product_id=orders.product_id
GROUP BY products.product_id, category_id, title,description, image, price, quantity

The results of this query get fed back to my PHP code which loops through them printing each out onto a web page. I'll just have it check with each one that quantity < order_count...if it is then I'll just refrain from printing it.

[mralston]

"Air Code"... so that's the technical term.
I'll have to remember that!

[PilgrimPete]

Did it work?

I think I misunderstood - and failed to make myself understood... I didn't realise that you were firing that SQL string at the database from code. I thought it was a stored query on the back-end, which was where I was coming from with the two part view concept... sorry.

PS I can't claim any credit for 'air code' - I stole it from Hack.

[mralston]

Yeah. The last bit of code I posted does what I need. It's a bit of a messy way of doing it, but I don't think I have the choice. Thanks.