CODEWITHSUNDEEP
mysqlsqlattributes
Ulrik McArdle
Ulrik McArdle

Reputation: 664

Selecting a value from multiple rows in MySQL

I have a little problem with my SQL sentence. I have a table with a product_id and a flag_id, now I want to get the product_id which matches all the flags specified. I know you have to inner join it self, to match more than one, but I don't know the exact SQL for it.

Table for flags

   product_id | flag_id
   1            1
   1            51
   1            23
   2            1
   2            51
   3            1

I would like to get all products which have flag_id 1, 51 and 23.

Upvotes: 1

Views: 54

Answers (4)

guruprasad
guruprasad

Reputation: 1

Try:

SELECT *
FROM TABLE_NAME A INNER JOIN TABLE_NAME B ON A.product_id = B.product_id

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

get the product_id which matches all the flags specified

This problem is called Relational Division. One way to solve it, is to do this:

  • GROUP BY product_id .
  • Use the IN predicate to specify which flags to match.
  • Use the HAVING clause to ensure the flags each product have,

like this:

SELECT product_id
FROM flags
WHERE flag_id IN(1, 51, 23)
GROUP BY product_id
HAVING COUNT(DISTINCT flag_id) = 3

The HAVING clause will ensure that the selected product_id must have both the three flags, if it has only one or two of them it will be eliminated.

See it in action here:

This will give you only:

| PRODUCT_ID |
--------------
|          1 |

Upvotes: 5

epoch
epoch

Reputation: 16615

Firstly it would help if you can specify what you have tried before, but as I understood you need to get products with certain flags, so you can just use WHERE:

SELECT product_id FROM Product WHERE flag_id IN (1,2,3,4,5)

Upvotes: 0

Omesh
Omesh

Reputation: 29121

try this:

SELECT *
FROM your_table
WHERE flag_id IN(1,2,..);

Upvotes: 0

Related Questions