Double left joins with conditions

mikebrown · #1 (**permalink**) 10-25-06, 13:38

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?

r937 · #2 (**permalink**) 10-25-06, 14:11

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

mikebrown · #3 (**permalink**) 10-25-06, 14:25

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.

r937 · #4 (**permalink**) 10-25-06, 14:50

thanks

that's the nicest kudos i've received in a while

Internet Services

Web Development

Digital Marketing

Consumer Tech