Mysql "IN" as AND

Question

Hi,

I would like to know if a mysql IN can be used as a AND statement:

SELECT item_id
FROM table_items as a i
WHERE i.attribute_id
IN (
  SELECT attribute_id
  FROM table_attribute as a
  WHERE [..]
)

I want to select the items from table_b who contains all the values selected in table_a. In other words the previous query is selecting with a OR I want it to be a AND.

Answer 1

IN is like an OR operator. It's not an AND. So it's more like Get me rows from table B where IDs are either 1, 2 or 3. AND operator wouldn't work anyway, because a certain row with some ID has only one value and not multiple values at the same time. AND would mean that a particular row ID should have multiple values.

Use table JOIN instead

If I understand what you'd like to do, then you should use an INNER JOIN on these two tables:

SELECT b.ID
FROM TableB AS b
  JOIN TableA AS a
  ON (a.ID = b.ID)

This way, you'll get those rows from TableB that have a corresponding row in TableA. If your tables are related and TableA is a subset of TableB (so TableB is primary key, and IDs in TableA are foreign keys pointing to TableB), than this is the solution for you.

Of course you will have to change table names as well as table columns.

Check MySql documentation on JOIN syntax.