Re: Double join in SQL query - R:Base specific


"Carl Colijn" <c.colijn@xxxxxxxxxxx> wrote in message
news:xn0frcbrs13ke0v000@xxxxxxxxxxxxxx
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"




.

Relevant Pages

  • Double join in SQL query - R:Base specific
    ... through the ODBC driver, and I prefer not to use an intermediate query ... in the database itself that I can call. ... WHERE a.x="some criteria" ... This gave me a "syntax error - 2367" when run through ODBC, ...
    (comp.databases)
  • Re: Double join in SQL query - R:Base specific
    ... through the ODBC driver, and I prefer not to use an intermediate query ... in the database itself that I can call. ... WHERE a.x="some criteria" ... This gave me a "syntax error - 2367" when run through ODBC, ...
    (comp.databases)
  • Re: Process wont die when form is closed.
    ... The database is a Pervasive v.9 with an ODBC driver. ... The DataGridView works great except when I'm done and I click the X to close ... Or is the background thread just an automagic thing and I have to insert the IsBackground property somewhere in order for the code to shutdown properly? ... In this case it means that the code is stucked in the main thread, probably in the Pervasive ODBC driver. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: improving performance by indexing query criteria fields
    ... establish relationships first in the relationships window. ... My advice is to index fields that you use to specify criteria and sort orders. ... important in a multi-user database, where the back-end is on a file server. ... appropriate records (assuming the query optimizer can use the index). ...
    (microsoft.public.access.setupconfig)
  • Re: improving performance by indexing query criteria fields
    ... establish relationships first in the relationships window. ... My advice is to index fields that you use to specify criteria and sort orders. ... important in a multi-user database, where the back-end is on a file server. ... appropriate records (assuming the query optimizer can use the index). ...
    (microsoft.public.access.queries)