dcsimg
Results 1 to 18 of 18

Thread: Comparing the values returned by two sub-queries

  1. #1

    Thread Starter
    Addicted Member mralston's Avatar
    Join Date
    Aug 2002
    Location
    Altrincham Nr Manchester, England
    Posts
    141

    Unhappy Comparing the values returned by two sub-queries

    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
    
    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?

  2. #2
    Frenzied Member PilgrimPete's Avatar
    Join Date
    Feb 2002
    Posts
    1,313
    Could you rewrite the subquery a bit like this?
    Code:
    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;

  3. #3
    Frenzied Member swatty's Avatar
    Join Date
    Aug 2002
    Location
    somewhere on earth
    Posts
    1,478
    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.
    Code:
    If Question = Incomplete Then
       AnswerNextOne
    Else
       ReplyIfKnown
    End If
    cu Swatty

  4. #4

    Thread Starter
    Addicted Member mralston's Avatar
    Join Date
    Aug 2002
    Location
    Altrincham Nr Manchester, England
    Posts
    141
    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...

    Code:
    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

  5. #5
    Frenzied Member PilgrimPete's Avatar
    Join Date
    Feb 2002
    Posts
    1,313
    First up, I appear to have a parentheses match-up error in my query; it should be:
    Code:
    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.

  6. #6
    Frenzied Member PilgrimPete's Avatar
    Join Date
    Feb 2002
    Posts
    1,313
    How about some creative joining.
    Will this work..?
    Code:
    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;

  7. #7

    Thread Starter
    Addicted Member mralston's Avatar
    Join Date
    Aug 2002
    Location
    Altrincham Nr Manchester, England
    Posts
    141
    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.

  8. #8

    Thread Starter
    Addicted Member mralston's Avatar
    Join Date
    Aug 2002
    Location
    Altrincham Nr Manchester, England
    Posts
    141
    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):
    Code:
    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.

  9. #9

    Thread Starter
    Addicted Member mralston's Avatar
    Join Date
    Aug 2002
    Location
    Altrincham Nr Manchester, England
    Posts
    141
    Hmmm... this article explains why my original query didn't work.

  10. #10
    Frenzied Member PilgrimPete's Avatar
    Join Date
    Feb 2002
    Posts
    1,313
    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:
    Code:
    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;

  11. #11

    Thread Starter
    Addicted Member mralston's Avatar
    Join Date
    Aug 2002
    Location
    Altrincham Nr Manchester, England
    Posts
    141
    You can betcha life that MySQL doesn't support views.
    Here goes nothing...

  12. #12

    Thread Starter
    Addicted Member mralston's Avatar
    Join Date
    Aug 2002
    Location
    Altrincham Nr Manchester, England
    Posts
    141
    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...

  13. #13
    Frenzied Member PilgrimPete's Avatar
    Join Date
    Feb 2002
    Posts
    1,313
    Yeah, but before you do please note my inability to write SQL. Ooooops:
    Code:
    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...

  14. #14
    Frenzied Member PilgrimPete's Avatar
    Join Date
    Feb 2002
    Posts
    1,313
    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.

  15. #15

    Thread Starter
    Addicted Member mralston's Avatar
    Join Date
    Aug 2002
    Location
    Altrincham Nr Manchester, England
    Posts
    141
    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)...

    Code:
    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.

  16. #16

    Thread Starter
    Addicted Member mralston's Avatar
    Join Date
    Aug 2002
    Location
    Altrincham Nr Manchester, England
    Posts
    141
    "Air Code"... so that's the technical term.
    I'll have to remember that!

  17. #17
    Frenzied Member PilgrimPete's Avatar
    Join Date
    Feb 2002
    Posts
    1,313
    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.

  18. #18

    Thread Starter
    Addicted Member mralston's Avatar
    Join Date
    Aug 2002
    Location
    Altrincham Nr Manchester, England
    Posts
    141
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width