Jason, I have never tried a query of your type where there were a column in each table with the same name being required in the final result. Perhaps you can request an enhancement to make this happen. In the mean time to make your query work consider creating a single table view of the columns from either record_group table or schedule table.
CREATE VIEW Record_Group_view (rg_id,rg_name,rg_status) AS SELECT rg_id,rg_name,status_name FROM Record_Group. Then you could do: BROWSE rg_id,rg_name,rg_status,schedule_id,sched_name,j1.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 Note!! the "BROWSE SELECT" at the beginning of your example should be either a BROWSE or a SELECT. If necessary you could include a WHERE clause in the single table VIEW to further restrict the values returned in the view. Ideally your three tables would have had separate names for "status_name" i.e. rg_status, sched_status, ser_status. Just remember that if you use these tables in a FORM or REPORT using a parent/child relationship you many not get the results intended as for example record_group = parent, schedule = child. rg_id and status_name would be common columns and both the rg_id and status_name of the parent and child must match for the child record to appear. Jim Bentley, American Celiac Society 1-504-737-3293 ________________________________ From: Jason Kramer <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Wednesday, November 21, 2012 10:44 AM Subject: [RBASE-L] - SQL Query question 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)
