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
(SELECT COUNT(*) FROM orders WHERE product_id=prod_id) < (SELECT quantity FROM products WHERE product_id=prod_id)
ORDER BY title, description;
[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