SQL syntax error that I'm unable to tell

[crowden]

Hi DB guys!

I'm trying to query from 3 tables; events, events_reg and hotel_reg. Events is the main table, with events_reg joining on a foreign key, and hotel_reg joings with events_reg on a foreign key. However, hotel_reg is not always going to have a record to join so it's throwing off my results.

Am I correct to use OUTER JOIN for this? Also, I'm getting this syntax error and cannot determine what it is exactly.

Query:

SELECT DISTINCT(er_id), event_name, team_company, title, name,lastname, event_type, event_deadline, sdate, assigned_fk, team_leader, STRCMP(assigned_fk,0) AS sort, CURDATE() 
FROM (events INNER JOIN event_reg ON event_reg.event_fk = events.event_id) 
OUTER JOIN hotel_reg ON hotel_reg.er_fk = event_reg.er_id 
WHERE event_deadline >= CURDATE() AND reg_status='New' AND assigned_fk IS NULL
ORDER BY sort,team_leader DESC,sdate,event_name,team_company

Here's my 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 'OUTER JOIN hotel_reg ON hotel_reg.er_fk = event_reg.er_id WHERE event_deadline >' at line 1

What's wrong with that? Thanks for any help.

[r937]

first of all, DISTINCT is not a function, so let's get rid of that

secondly, it is recommended to qualify every column with its table name

that way you will be able to read the query and figure out which table each column comes from, which i cannot do right now

your problem was due to omitting the LEFT keyword in LEFT OUTER

LEFT is mandatory, it's actually OUTER that's optional

;o)

SELECT er_id
     , event_name
     , team_company
     , title
     , name
     , lastname
     , event_type
     , event_deadline
     , sdate
     , assigned_fk
     , team_leader
     , STRCMP(assigned_fk,0) AS sort
     , CURDATE() 
  FROM events 
INNER 
  JOIN event_reg 
    ON event_reg.event_fk = events.event_id 
LEFT OUTER 
  JOIN hotel_reg 
    ON hotel_reg.er_fk = event_reg.er_id 
 WHERE event_deadline >= CURDATE() 
   AND reg_status='New' 
   AND assigned_fk IS NULL
ORDER 
    BY sort
     , team_leader DESC
     , sdate
     , event_name
     , team_company