1

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.

Roch
  • 21,741
  • 29
  • 77
  • 120
  • 1
    What are item_id - integers? I don't understand how one row in table_b can contain more than one item_id from table_a? – Rup Aug 19 '10 at 13:56
  • Indeed, this is quite confusing. Perhaps post some sample data to illustrate the issue? – Brendan Bullen Aug 19 '10 at 13:59
  • what the relation of the tables ? can add schema – Haim Evgi Aug 19 '10 at 13:59
  • If I read the question correctly, you want to find all rows in the `table_items` which has all of the attributes selected by the second (inner) `SELECT` statement. Surely, either the inner select statement will get no rows, or one row, in which case the solution you have will be quite sufficient, or it will get more than one row, in which case the condition can never be satisfied since each row in `table_items` can only have one `attribute_id`. Or have I missed the point? – Brian Hooper Aug 19 '10 at 14:23

1 Answers1

5

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.

Robert Koritnik
  • 103,639
  • 52
  • 277
  • 404
  • I think that it's not marked as resolved because that approach would mean making a `JOIN` for every `attribute_id` from the `SELECT` written. That maybe would mean a bad performance (for sure), and if those `attribute_id` are got on the fly, it would be impossible to write all the `JOIN` by hand before knowing the result. So, it would be necessary some scripting language, just like PHP, and, even then, the performance would be awful if there were many `attribute_id` values. – Unapedra Apr 01 '16 at 11:17
  • However I think this could be solved with this other answer found here http://stackoverflow.com/questions/11636061/matching-all-values-in-in-clause which is exactly what is needed. Hope it may help, though this question is old. – Unapedra Apr 01 '16 at 11:27