Geek asks and answers

MySQL syntax error near UNION?

SELECT * 
FROM Activity AA
WHERE AA.act_id IN 
((SELECT A.act_id 
  FROM Activity A 
  WHERE A.user_id = 'lhfcws')
 UNION
 (SELECT J.act_id 
  FROM Joinin J 
  WHERE J.user_id = 'lhfcws'))
ORDER BY AA.act_time

ERROR MESSAGE: # 1064 - You have an error in the SQL syntax; check the manual corresponding to the version of MySQL server for correct use of the syntax near'UNION (SELECT J.act_id FROM Joinin J WHERE J.user_id = 'lhfcws')) ORDE' at line 7

Activity (act_id, user_id, act_name)
Joinin (act_id, user_id)

+5
source share
2 answers

The cause of your error is parens around select statements. You should write this as:

SELECT * 
FROM Activity AA
WHERE AA.act_id IN 
(SELECT A.act_id 
  FROM Activity A 
  WHERE A.user_id = 'lhfcws'
 UNION
 SELECT J.act_id 
  FROM Joinin J 
  WHERE J.user_id = 'lhfcws')
ORDER BY AA.act_time

But go ahead with @ Raphaël Althaus ideas to improve your request.

+6
source

Hmm, don’t think you need such a subquery

select * from Activity a
where a.user_id = 'lhfcws'
and exists (select null from Joinin j
where a.user_id = j.user_id);

do the same thing

Maybe you need another check

select * from Activity a
    where a.user_id = 'lhfcws'
    and exists (select null from Joinin j
    where a.user_id = j.user_id
    and a.act_id = j.act_id);

According to @Jonathan Leffler's comment (true)

select * from Activity a
        where a.user_id = 'lhfcws'
        or exists (select null from Joinin j
        where j.user_id = 'lhfcws'
        and a.act_id = j.act_id);
+2
source

More articles:


All Articles