Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    20

    Unanswered: difference of two queries

    hello,
    i don't understand why those two queries don't give the same
    result:
    1)
    Code:
    select name 
    from customer 
    where custid in
    (select custid
    from ord
    where ordid in (
    	(select ordid
    	from item,product
    	where item.prodid = product.prodid
    	and product.descrip like '%TENNIS%')
    	minus
    	(select ordid
    	from item,product
    	where item.prodid = product.prodid
    	and product.descrip not like '%TENNIS%')
    ));
    2)
    Code:
    select name
    from customer
    where custid in
    	(select custid 
    	from ord
    	where ordid in (
    		(select ordid
    		from item,product
    		where item.prodid = product.prodid
    		and product.descrip like '%TENNIS%')))
    minus
    select name
    from customer
    where custid in
    	(select custid 
    	from ord
    	where ordid in (
    		(select ordid
    		from item,product
    		where item.prodid = product.prodid
    		and product.descrip not like '%TENNIS%')));
    they should give the same result but they don't....

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's right, they don't

    nor should they

    perhaps you can explain why you think they should
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2008
    Posts
    20
    in (1) this subquery
    Code:
    (select ordid
    	from item,product
    	where item.prodid = product.prodid
    	and product.descrip like '%TENNIS%')
    	minus
    	(select ordid
    	from item,product
    	where item.prodid = product.prodid
    	and product.descrip not like '%TENNIS%')
    should return ordid of those ordids who ordered items with desctiption which contains the word 'TENNIS' minus those with description which does not contains the word 'TENNIS'
    and get the name of customers with those ordids.

    in (2) i first get the names who ordered items with desctiption which contains the word
    'TENNIS' minus names who ordered items with description which does not contains the word 'TENNIS'.

    isn't the same ?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    query 1 returns customers who have any orders for ~only~ tennis items

    query 2 returns customers who never ordered any non-tennis items


    look at this sample data --

    customer AAA order #123 contains only a tennis racquet
    customer AAA order #456 contains a tennis racquet and a football

    customer BBB order #789 contains a tennis racquet and tennis shorts
    customer BBB order #555 contains tennis balls

    query 1 returns both AAA and BBB

    query 2 returns only BBB
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2008
    Posts
    20
    i think i got it,
    thank you for explaining

  6. #6
    Join Date
    Nov 2008
    Posts
    20
    i think i got it,
    thank you for explaining

Posting Permissions

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