Results 1 to 2 of 2
  1. #1
    Join Date
    May 2011

    Unanswered: Look for advice on a query to return results which meet all criteria...

    Hi guys

    I'm having a little trouble with a query that needs to return only the rows that meet all criteria.
    The table I have basically contains 2 columns 'productId' and 'value'

    Now if I have 7 rows:-
    4 rows with productId = prod1 and value = 1,2,3 and 4.
    3 rows with productId = prod2 and value = 1,3,4 and 5.

    I want to be able to search for all productId where value = 2 or 3 but only return prod1 because only prod1 has rows with values of 2 and 3.

    If I use AND I get no results because value = 2 AND value = 3 isn't actually correct, if I use IN (or OR) something like IN(2,3) I get both prod1 and prod2 back because I'm saying 'rows with 2 or 3 in them'.

    What I need is something like an exclusive or that only returns the row(s) that meet all OR criteria .

    I hope this makes sense.



    Just to not that I think I've found a solution but it's a bit messy, so is there a tidy way of doing this? :-

    SELECT * FROM filters AS t1 WHERE t1.type='PROD_SIZE' AND t1.value = 3
    AND (SELECT s1.product_id FROM filters AS s1 WHERE s1.type = t1.type AND s1.product_id = t1.product_id AND s1.value = 4)
    Last edited by kappa_uk; 05-16-11 at 12:40.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    SELECT something
      FROM filters 
     WHERE product_id IN
           ( SELECT product_id
               FROM filters
              WHERE type = 'PROD_SIZE' AND value = 3
                 OR type = 'PROD_SIZE' AND value = 4 
                 BY product_id 
             HAVING COUNT(*) = 2 ) | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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