Syntax error in MySQL Join Query

Question

I'm getting a syntax error in MySQL query. Is MySQL and SQL server work differently? Can anyone suggest, what is wrong and where ?

select b.component, d.matter, d.bug, d.timestamp,  d.os 
from bugs.profiles p, ops_reports.BPR_TAG_DATA d
left join (Select * from bugs where product='test') b 
on d.bug=b.bug_id
where d.tagid = 6
and timestamp between "2014-04-21" and "2014-04-24"
and login_name like 'test'
and p.userid = d.user

Error Message 24/04/2014 23:14:10 0:00:00.037 MySQL Database Error: 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 'Select * from bugs where product='Conversions') as b on (d.bu 1 0

Do not mix implicit (comma-) join and explicit join syntax. In fact do not use comma-join syntax at all — Strawberry
– Strawberry, Commented Apr 24, 2014 at 22:32
Strawberry, Can you please describe little more or put your suggested query here, if possible — user3471169
– user3471169, Commented Apr 24, 2014 at 22:35
The error message appears to be for some SQL text that is different from the SQL text provided. The inline view (aliased as b) isn't necessary, and ditch the old-school comma syntax for the join operation. Use the JOIN keyword in place of the comma, and relocate join predicates from the WHERE clause to an appropriate ON clause. Also, best practice is to qualify all column references. (e.g. and b.timestamp BETWEEN... easier for the reader to decipher AND avoids query breaking with "ambiguous column" error when column of the same name later added to another table — spencer7593
– spencer7593, Commented Apr 24, 2014 at 22:46

Gordon Linoff · Accepted Answer · 2014-04-24 22:34:06Z

2

You should not mix implicit and explicit joins. A simple rule: just don't use commas in the from clause.

select b.component, d.matter, d.bug, d.timestamp, d.os 
from ops_reports.BPR_TAG_DATA d left join
     bugs b
     on b.product = 'test' and d.bug = b.bug_id left join
     bugs.profiles p
     on p.userid = d.user
where d.tagid = 6 and
      timestamp between '2014-04-21' and '2014-04-24' and
      login_name like 'test';

I also removed the subquery, moving the condition to the on clause. This makes the query more efficient. And changed the delimiters for the date constants to single quotes. Using double quotes for strings can lead to confusion.

EDIT:

All this said, the query in the question looks like it is syntactically correct. I notice that the error message does not refer to this exact query. The query has product='test') b and the error message has product='Conversions') as b. Perhaps there are other differences as well.

answered Apr 24, 2014 at 22:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3471169 Over a year ago

Thanks Gordon, the test was replaced, as i don't wanted to put the actual value. Sorry :) for confusion. It's working now.

Collectives™ on Stack Overflow

Syntax error in MySQL Join Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related