Question 217819 in Mysql Question posted in
The official MySQL documentation can be found here.

#1241 – Operand should contain 1 column(s) filtering value from other table mysql

#1241 – Operand should contain 1 column(s)

SELECT name 
FROM tabel 
WHERE n1 IN (
      SELECT key1,key2,key3 
      FROM tabel2
)

but if try :

SELECT name 
FROM tabel 
WHERE n1 IN ('1','2','5')

its work

thanks

Tags:

2

Answers


  1. 0

    You can not use queries which return more than 1 column if you want to use the result in an IN clause

    Try something like this if you want to match against all 3 Keys:

    SELECT name FROM tabel 
WHERE n1 IN (SELECT key1 FROM tabel2)
OR n1 IN (SELECT key2 FROM tabel2)
OR n1 IN (SELECT key3 FROM tabel2)

    Explanation:

    This works because its a list of values:

    WHERE n1 IN ('1','2','3')

    This does not work:

    WHERE n1 IN (SELECT key1, key2, key3 FROM tabel2)

    The query returns a list of results instead of single values:

    WHERE n1 IN (('1','5',',7'),('2','8',',72'),('11','52',',72'))
    Login or Signup to reply.
  2. 0

    The reason is, in your second working query, the IN ('1','2','5') returns a set of 3 values to test the predicate against.

    Your first query does not work because it’s invalid for use by the IN operator, which requires a set of values.

    If you think about it, the first query cannot possibly work because key1,key2,key3 are 3 different columns and each could be a different data type, so key1 could be an integer and key2 could be a date and key3 could be boolean….

    This query could return 1000 rows of 3 columns so 3000 potentially conflicting types of data, how would IN know what to do with that!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search