|
I am trying query three tables using LEFT OUTER JOINs. All three
tables have a field with the same name, and I need to include that
field from each table in my results. When I issue the query: BROWSE SELECT rg_id,rg_name,T1.status_name AS rg_status,schedule_id,sched_name,T2.status_name AS sched_status,series_id,ser_name,T3.status_name AS ser_status FROM (record_group T1 LEFT OUTER JOIN schedule T2 ON T1.rg_id=T2.rg_id) J1 LEFT OUTER JOIN series T3 ON J1.schedule_id=T3.schedule_id I get the correlation T1 not found. If I switch T1 and T2 in the first part of the select statement to be J1, then the query runs, but then rg_status and sched_status both end up containing the values from record_group.status_name. I recreated the tables in MS Access, and was able to run this query successfully: SELECT record_group.rg_id, record_group.rg_name, record_group.status_name, schedule.schedule_id, schedule.sched_name, schedule.status_name, series.series_id, series.ser_name, series.status_name FROM (record_group LEFT OUTER JOIN schedule ON record_group.rg_id = schedule.rg_id) LEFT OUTER JOIN series ON schedule.schedule_id = series.schedule_id; The only change is using tablename.columnname in place of T1 style correlations. Is there a way to "descend" through the corerlations in R:Base? I tried J1.T1.status_name, but got an error that database J1 did not exist, so I know that I can't just chain the correlations. I know that I can create three intermediate queries and then join the results together, but I would rather make just one query. Thanks, Jason -- Jason Kramer University Archives and Records Management 002 Pearson Hall (302) 831 - 3127 (voice) (302) 831 - 6903 (fax) |
