Comparing the values returned by two sub-queries

[matthewralston]

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?

I'm also running this question at

http://www.vbforums.com/showthread.php?s=&postid=1239840#post1239840

 

[KarveR]

try this :

SELECT
p.product_id,
p.title,
p.description,
p.image,
p.price,
p.start_date,
p.lifetime,
p.renewal_date
count(o.order_id),
p.quantity

FROM
products p,
orders o
WHERE
category_id=8
AND
p.product_id = o.product_id
ORDER BY
p.title,
p.description; ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[matthewralston]

I need it to only return the records where

count(o.order_id) < p.quantity

And if I put that into the WHERE clause MySQL won't run it.

 

[KarveR]

SELECT
p.product_id,
p.title,
p.description,
p.image,
p.price,
p.start_date,
p.lifetime,
p.renewal_date
@count_ord:=count(o.order_id),
p.quantity

FROM
products p,
orders o
WHERE
category_id=8
AND
p.product_id = o.product_id
AND
@count_ord < p.quantity
ORDER BY
p.title,
p.description; ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[matthewralston]

MySQL doesn't like the @ (and possibly the := ) operator. I don't think it has an equivalent.

 

[KarveR]

Looks like I missed the comma from the line containing p.renewal_date just above it.

note: @variable:=value
Set a user variable within mysql, this will remain for the session, to the value stated.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[matthewralston]

It wanted a GROUP BY clause, so I put one in. Then it would run, but returns zero records. I tried using an outer join but still no luck. This is what I've got at the moment...

SELECT
p.product_id,
p.title,
p.description,
p.image,
p.price,
p.start_date,
p.lifetime,
p.renewal_date,
p.quantity,
@count_ord:=count(o.order_id)
FROM
products p LEFT OUTER JOIN orders o ON p.product_id=o.product_id
WHERE
category_id=1
AND
@count_ord < p.quantity
GROUP BY
p.product_id,
p.title,
p.description,
p.image,
p.price,
p.start_date,
p.lifetime,
p.renewal_date,
p.quantity
ORDER BY
p.title,
p.description;

 

[KarveR]

your WHERE clause must join the tables by a matchin key, or it will fail:

AND
p.product_id = o.product_id ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[matthewralston]

The ON clause of the FROM statement does this. Putting in in the WHERE statement makes no difference.

 

[KarveR]

does grouping by so many columns leave any results in the first place ? do yo get any results with all the groupage? ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[matthewralston]

GROUP BY clause not being something I understand, I normally just group by everything in the SELECT fields...normally works!