CODEWITHSUNDEEP
mysqlsqlsyntax
Lhfcws
Lhfcws

Reputation: 312

Syntax error in MySQL 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 your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use 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)

Upvotes: 6

Views: 13571

Answers (3)

Elyor
Elyor

Reputation: 5532

Don't use ORDER BY before UNION

Upvotes: 1

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

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);

sould do the same

maybe you need one more 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);

Acording to @Jonathan Leffler's (true) remark

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);

Upvotes: 3

Ilion
Ilion

Reputation: 6882

The reason for your error is the parens around the select statements. You should write it 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 do go over @Raphaël Althaus ideas for improving your query.

Upvotes: 8

Related Questions