Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2006
    Posts
    9

    Question Unanswered: Double left joins with conditions

    I have a setup in an Access database with linked tables to Oracle (its a remote application database that is synced when a connection is available). I have to perform inserts, updates, and deletes based upon records that have changed since the last update. In the examples of inserting records into oracle, I find records in access that do not exist in oracle and meet the date condition. Where I run into problems are in tables like [events]: I need to only be updating, inserting, and deleting events for a specific county but the only place county is designated is in the location table that events are tied to through lctn_id. I just can't figure out how to involve the location table join with the events table join and the necessary conditions.

    locations (and locations_ora)
    lctn_id, cnty_nbr, lctn_nm..., rec_dt

    events (and events_ora)
    evnt_id, lctn_id, evnt_txt, ..., rec_dt


    Example of format used for another table:
    INSERT INTO locations_ora SELECT DISTINCTROW a.* FROM locations a LEFT JOIN locations o ON a.lctn_id=o.lctn_id WHERE (o.lctn_id IS NULL) AND a.rec_dt>#8/17/2000 1:11:11 AM# AND a.cnty_nbr=5

    How I would like to get events working:
    INSERT INTO events_ora SELECT DISTINCTROW a.* FROM events AS a LEFT JOIN events_ora AS o ON a.evnt_id=o.evnt_id WHERE (o.evnt_id IS NULL) AND a.rec_dt>#8/17/2000 1:11:11 AM# AND {some way to only get events tied locations in a specific county}
    -- or even --
    INSERT INTO events_ora SELECT DISTINCTROW a.* FROM (SELECT a.* FROM events a LEFT JOIN locations l ON b.lctn_id=l.lctn_id WHERE l.cnty_nbr=5 ) LEFT JOIN events_ora o ON a.evnt_id=o.evnt_id WHERE (o.evnt_id IS NULL) AND a.rec_dt>#1/17/1979 1:11:11 AM#
    (I thought this one would work but Access blows up on the SELECT after the FROM.)

    Can anyone point me in the right direction?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    INSERT 
      INTO events_ora 
    SELECT a.* 
      FROM (
           events a 
    inner 
      JOIN locations l 
        ON (
           l.lctn_id = a.lctn_id 
       and l.cnty_nbr = 5 
           )
           )  
    LEFT 
      JOIN events_ora o 
        ON o.evnt_id = a.evnt_id 
     WHERE a.rec_dt>#1/17/1979
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2006
    Posts
    9
    The traffic signals of North Carolina thank you immensely!

    Seeing how this is done correctly and in the reading I've done on your site (since I figured you would be the likely responder to my thread) has definitely increased my understanding of how to work with SQL. You will now be added to the Whiteboard of Fame in conference room C-011.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks

    that's the nicest kudos i've received in a while
    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
  •