Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    20

    Unanswered: SQL, left join problem...

    Hi,

    I'm trying to make an SQL query from Delphi application to an access database with an left join. Can't seem to get it working, I get "syntax error in JOIN operation". This is what I tried:

    Code:
     SELECT
      user.firstname + " " + user.surname as name,
      event.date,
      event.location,
      event.temperature,
      event.distance,
      event.time,
      event.condition,
      event.info,
      target.distance as target_distance,
      gear.model + " - " +  gear.type + as gear_modeltype
     FROM user, event, gear
     LEFT JOIN target 
     ON event.date = target.date AND event.user_id = target.user_id
     WHERE
      (
       (event.user_id = user.user_id) AND 
       (event.gear_id = gear.gear_id) AND 
       (event.user_id = 1) AND 
       (event.date BETWEEN #mm/dd/yyyy# AND #mm/dd/yyyy#)
      )
     ORDER BY
      event.date
    So what the SQL should do is select all records in event table from a certain user_id (1) with given day span and select target.distance from target table if it has a record with the same date and user_id (1) as the record in event table.

    Still learning.. thanks in advance for any info!



  2. #2
    Join Date
    Jul 2004
    Posts
    20
    Obviously there's something wrong with the sql-sentence, anyone got any clue?

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I tried a similar query in Access just now, and got a similar error message.

    The problem lies in the way your From statetement is constructed.
    Instead of:

    Code:
    FROM user, event, gear
     LEFT JOIN target 
     ON event.date = target.date AND event.user_id = target.user_id
     WHERE
      (
       (event.user_id = user.user_id) AND 
       (event.gear_id = gear.gear_id) AND 
       (event.user_id = 1) AND 
       (event.date BETWEEN #mm/dd/yyyy# AND #mm/dd/yyyy#)
      )
    try using:
    Code:
    FROM user inner join event on user.user_id = event.user_id 
       inner join gear on event.gear_id = gear.gear_id 
     LEFT JOIN target 
     ON event.date = target.date AND event.user_id = target.user_id
     WHERE
      (
       (event.user_id = 1) AND 
       (event.date BETWEEN #mm/dd/yyyy# AND #mm/dd/yyyy#)
      )
    Inspiration Through Fermentation

  4. #4
    Join Date
    Jul 2004
    Posts
    20
    Hi RedNeckGeek and thanks for the reply,

    I tried the method you posted, but got 'syntax error (missing an operator) in query expression' followed by the sql part marked in red below.. there isn't any missing operators tho.. do I need some brackets on there or wot?

    Code:
    SELECT
      hiihtokerta.paivamaara,
      hiihtokerta.sijainti,
      hiihtokerta.lampotila,
      hiihtokerta.lumimaara,
      hiihtokerta.matka,
      hiihtokerta.aika,
      hiihtokerta.nopeus,
      hiihtokerta.paino,
      hiihtokerta.kunto,
      hiihtokerta.lisatiedot,
      tavoite.matka as tavoitematka,
      hiihtaja.etunimi + " " + hiihtaja.sukunimi as nimi,
      suksi.merkki + " " + suksi.malli + " - " + suksi.tyyppi as suksimerkki,
      voide.merkki + " - " + voide.tyyppi as voidemerkki
    FROM
      hiihtokerta
    INNER JOIN
      hiihtaja ON hiihtokerta.hiihtaja_id = hiihtaja.hiihtaja_id
    INNER JOIN 
      suksi ON hiihtokerta.suksi_id = suksi.suksi_id
    INNER JOIN 
      voide ON hiihtokerta.voide_id = voide.voide_id
    LEFT JOIN
      tavoite ON (hiihtokerta.paivamaara = tavoite.paivamaara AND
                  hiihtokerta.hiihtaja_id = tavoite.hiihtaja_id)
    WHERE
      (
       (hiihtokerta.hiihtaja_id = '+ from_variable +') AND 
       (hiihtokerta.paivamaara BETWEEN #mm/dd/yyyy# AND #mm/dd/yyyy#)
      )
    ORDER BY
      hiihtokerta.paivamaara
    btw, its Finnish




  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, a cross-posted answer

    http://www.dbforums.com/t1118071.html

    but you started it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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