Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Unanswered: Substitute of 'IN' Operator

    Hello folks,

    I've got a table like this

    PRODUCT_ID LABEL_ID
    ----------- -----------
    6 1
    6 2
    7 1
    1 1
    2 1

    I need to have a select statement to take all the products with the LABEL_ID containing 1 AND 2. I can't use the IN operator as it will return me results with LABEL_ID 1 OR 2. Is there any solution to this? Sorry I'm relatively new to mysql and not familiar with the select statements.

    I'm currently working on something like a search engine. Hope to have some good advice here

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Microsoft SQL isn't MySQL, but you can often create a statement that will work for both of them.

    Your problem sounds a lot like homework. Is it?

    -PatP

  3. #3
    Join Date
    Jan 2005
    Posts
    2
    Nope, sorry if I've mixed them both up. I'm working as a Product Manager and currently need to do a search engine for all the products i cater.

    I heard of something call nested select statement that can solve this problem. Is it true?

    Sorry if I sound nooberish

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, the nested select will solve this problem nicely. Something like:
    Code:
    SELECT *
       FROM myTable AS a1
       INNER JOIN myTable AS a2
          ON (a2.product_id = a1.product_id
          AND 2 = a2.lable_id)
       WHERE  1 = label_id
    This is a little bit "messy" in that it brings you back all of both rows, but its the most flexible way to get everything that you might want, and lets you figure out what is useful to you.

    It uses two SQL aliases to allow you to refer to the same table twice within a single SELECT statement... The a1 alias refers to the row with a label of 1 and the a2 alias refers to the row with a label of 2 where both rows have the same product id. This query only returns rows for products that have both a row with a label of 1 and a row with a label of two.

    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Off the top of my head late at night...

    Code:
    select	Product_ID
    from	YourTable
    where	LABEL_ID in (1, 2)
    group by Product_ID
    having count(distinct LABEL_ID) = 2
    There are other simpler methods dealing with special cases, but this should be extensible to N comparions values.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    There are other simpler methods dealing with special cases, but this should be extensible to N comparions values.
    You are positively deviant! I always knew that I liked you!

    -PatP

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, I felt there was something special between us from our very first posts.

    ...or did you mean deviant in a more allegorical sense?

    Better just snipe this whole thread...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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