Hi group,
I have an R:Base specific question, but didn't find an R:Base specific
newsgroup. I do not know if there is any; if so, could you give me a
pointer?
In case someone here knows the answer to my question:
I need to create a query that selects a record from table a, then left
join it with table b and perform another left join with table c as
well. Basically I need a double left join. It needs to be passed
through the ODBC driver, and I prefer not to use an intermediate query
in the database itself that I can call.
I already tried to chain the joins like:
SELECT *
FROM a
LEFT JOIN b ON a.p=b.p
LEFT JOIN c ON a.q=c.q
WHERE a.x="some criteria"
This gave me a "syntax error - 2367" when run through ODBC, and an
"error - the syntax is incorrect for SELECT (2045)" when run directly
in the database on the prompt. In MS Access you can do this by using
parenthesis around one of the joins, so I tried that as well;
SELECT *
FROM (
a LEFT JOIN b ON a.p=b.p
) LEFT JOIN c ON a.q=c.q
WHERE a.x="some criteria"
This gave me exactly the same errors.
I also tried a direct select on the three tables, mathing the records
using the WHERE clause;
SELECT *
FROM a, b, c
WHERE a.p=b.p AND a.q=c.q AND a.x="some criteria"
but then I need a left join as not all records in table a have matching
records in tables b and c.
Since I'm not well R:Base-SQL versed, here's where I get stumped and my
thoughts dry out... Does anyone have a suggestion, except for building
an intermediate view on the database side to use?
--
Kind regards,
Carl Colijn
TwoLogs - IT Services and Product Development
A natural choice!
http://www.twologs.com
TimeTraces: the powerfull and versatile time registration system!
http://www.twologs.com/en/products/timetraces.asp
|
