CODEWITHSUNDEEP
mysqlsubquery
Enki
Enki

Reputation: 1695

MySQL Subquery IN results

I want to use ids from query on my_table to select all field_b rows from my_table_2.

  SELECT field_b
  FROM my_table_2
  WHERE my_id_2 IN (
         SELECT my_id FROM my_table WHERE field_a = 1234
  );

I get ERROR 1054 (42S22): Unknown column 'my_id_2' in 'IN/ALL/ANY subquery' executing the above query.

Below are example of my tables for reference:

  mysql> select * from my_table;
    +-------+---------+
    | my_id | field_a |
    +-------+---------+
    |     1 |    1234 |
    +-------+---------+
    |     2 |    1234 |
    +-------+---------+
    2 row in set (0.00 sec)

mysql> select * from my_table_2;
+-------+-----------+
| my_id_2 | field_b |
+---------+---------+
|     1   |  5678   |
+-------+-----------+
1 row in set (0.00 sec)

Upvotes: 2

Views: 2060

Answers (2)

Andrej
Andrej

Reputation: 7504

Another way

SELECT field_b
FROM my_table_2 m2
WHERE EXISTS (
     SELECT my_id FROM my_table 
     WHERE field_a = 1234 and m2.my_id_2=my_table.my_id
);

Upvotes: 3

Marc B
Marc B

Reputation: 360592

That query can be rewritten as a simple join operation:

SELECT field_b
FROM my_table_2
INNER JOIN my_table ON my_table_2.my_id_2 = my_table.my_id
WHERE field_a = 1234;

Upvotes: 1

Related Questions