问题描述:

I have the following sql statement and I want to fetch data from users table where user id is NOT in another table of column_one and column_two.

I dont know why this statement return an error:

This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

So with this error I remove the LIMIT statement and now it says :

Operand should contain 1 column(s)

I can see SQL wants me to select only one column in the sub-query but this is not what I want.

How can I make it work avoiding all these errors and return a valid sql statement with my filtering?

This is the SQL code I tried:

SELECT u.username, u.firstname, u.lastname,u.id,u.school

FROM users u

WHERE u.id NOT IN(

SELECT user_two,user_one FROM friends

WHERE user_one !='8'

OR user_two !='8'

)

网友答案:

Logically You have an asimmetric use of IN operator

the two part of the in clause must contain the same number of component

You should use

SELECT u.username, u.firstname, u.lastname,u.id,u.school
    FROM users u
        WHERE u.id NOT IN( 
            SELECT  id  FROM friends 
                WHERE user_one !='8' 
                    OR user_two  !='8'
        )

or

SELECT u.username, u.firstname, u.lastname,u.id,u.school
    FROM users u
        WHERE (u.id1, u.id2) NOT IN( 
            SELECT  user_two,user_one  FROM friends 
                WHERE user_one !='8' 
                    OR user_two  !='8'
        )

or the result is impredictable

相关阅读:
Top