sql syntax wrong?-CodePudding

I have 2 tables (users and friends):

users   : user_sid  | user_status
friends : user_sid  | user2_sid | type

i want to get list of online friends so i use this syntax :

SELECT user_sid 
FROM users 
WHERE users.user_status>0 
AND users.user_sid IN (
    (SELECT user_sid FROM friends WHERE user2_sid = '5604075088818445472' AND type=1) 
    UNION 
    (SELECT user2_sid FROM friends WHERE user_sid = '5604075088818445472' AND type=1)
) LIMIT 20 OFFSET 0

but i got this warning:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION (SELECT user2_sid FROM friends WHERE user_sid = '56040750888184454...' at line 1

what i undertsand is

((SELECT user_sid FROM friends WHERE user2_sid = '5604075088818445472' AND type=1))

return empty and that give warning in mariadb.

anyone can help ? thanks

i want to get list of online friends without warning if there is no friends.

CodePudding user response：

Syntax errors can't be due to what the subquery returns, because syntax is checked before executing anything.

The problem is that you have too many parentheses. It thinks you're doing IN (list of values) rather than IN (subquery). Get rid of the extra parentheses around each query in the UNION.

SELECT user_sid 
FROM users 
WHERE users.user_status>0 
AND users.user_sid IN (
    SELECT user_sid FROM friends WHERE user2_sid = '5604075088818445472' AND type=1
    UNION 
    SELECT user2_sid FROM friends WHERE user_sid = '5604075088818445472' AND type=1
) 
LIMIT 20 OFFSET 0