SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL syntax error that I'm unable to tell

    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:
    Code SQL:
    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.
    No, I REALLY dislike having to use Joomla.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •