Page 1 of 1

Comparing the values returned by two sub-queries

Posted: Tue Oct 29, 2002 3:52 am
by mralston
I've got a MySQL database, with two tables that we're interested in:

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

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:

Code: Select all


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

(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 have also posted this question at ... ost1239840

Posted: Tue Oct 29, 2002 4:23 am
by DoppyNL
mysql doesn't support subselects.
so you cant:

select ...... select .....

You will have to make a temporary table in mysql or php for that.

For more details see mysql manual for subselects Where it is explained completely and some possible solutions are given.

Greetz Daan

Posted: Tue Oct 29, 2002 6:39 am
by mralston
That article was way over my head. :(