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
Yes, the nested select will solve this problem nicely. Something like:
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.