Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    21

    Unanswered: Select a field if mathes to 2 or more criteria

    Hi

    I have this db table

    prod_id cat_id
    1---------1
    2---------1
    3---------1
    1---------2

    I want the query to search for a product that belongs to 2 categories (having the cat_id's). In this case search for products that belongs to cat_id's 1 and 2.

    in this example is prod_id=1 (belongs both to 1 and 2).

    Is this possible to be done with a query ??

    Plz help

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Code:
    select a.prod_id
       from ur_table a
    where exists (select 1 from ur_table b
                       where a.prod_id <> b.prod_id
                           and a.cat_id = b.cat_id)
    Dave

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I'm not sure the previous query will work (apologies if it does). Anyway something like the following might work better and be easier to expand if you need to match against 3 categories etc:
    Code:
    select   prod_id
    from     ur_table
    where    cat_id in ( 1,2 )
    group by prod_id
    having   count( distinct cat_id ) = 2;
    Mike

  4. #4
    Join Date
    Nov 2009
    Posts
    21
    Thanks a lot guys
    I used Mike's solution and it works !!

Posting Permissions

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