CODEWITHSUNDEEP
mysqlsql
user3471169
user3471169

Reputation: 69

Syntax error in MySQL Join Query

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

Upvotes: 0

Views: 180

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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.

Upvotes: 2

Related Questions