Comparing the values returned by two sub-queries

Codes here !

Moderators: macek, egami, gesf

User avatar
mralston
New php-forum User
New php-forum User
Posts: 13
Joined: Tue Oct 29, 2002 3:47 am
Location: Altrincham, Cheshire, England
Contact:

Comparing the values returned by two sub-queries

Postby mralston » Tue Oct 29, 2002 3:52 am

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:

Code: Select all

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 have also posted this question at http://www.vbforums.com/showthread.php? ... ost1239840

DoppyNL

Postby DoppyNL » Tue Oct 29, 2002 4:23 am

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

User avatar
mralston
New php-forum User
New php-forum User
Posts: 13
Joined: Tue Oct 29, 2002 3:47 am
Location: Altrincham, Cheshire, England
Contact:

Postby mralston » Tue Oct 29, 2002 6:39 am

That article was way over my head. :(


Return to “mySQL & php coding”

Who is online

Users browsing this forum: No registered users and 0 guests

cron