"Carl Colijn" <c.colijn@[EMAIL PROTECTED]
> wrote in message
news:xn0frcbrs13ke0v000@[EMAIL PROTECTED]
> 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?
>
I don't know R:Base, but I would give something like this a try:
SELECT *
FROM (select * from
a LEFT JOIN b ON a.p=b.p
) as x
LEFT JOIN c ON x.q=c.q
WHERE a.x="some criteria"
|
