Double join in SQL query - R:Base specific

#21

Tonkuma wrote:

Quote:

Although you already solved the issue by yourself without using join,
I would like to comment on one more thing.
I thought that multiple outer(and/or inner) joins to join more than
two tables would be allowed R:BASE 7.6 or later.

I looked into R:BASE Online Documentation
http://www.rsyntax.com/

and compared each description of OUTER JOIN in the following V/R.
R:BASE Turbo V-8 for Windows
R:BASE 7.6 for Windows
R:BASE 7.5 for Windows
R:BASE 7.1 for Windows
R:BASE 6.5++ for Windows

Following sentence(and INNER JOIN) was inclueded in R:BASE 7.6 and
Turbo V-8, but I couldn't find such statement(and INNER JOIN) in
R:BASE 7.5 or prior.

About JOIN
When you perform a SQL JOIN, ..... You can use multiple joins in the
same SQL statement to query data from as many tables as you like.

Hi Tonkuma,

So it indeed seems the database needs to be upgraded to a more recent
version for multiple joins to work. However, I do not have any control
over this; I'm just programming some queries to the database from
outside, and the database itself (at version 6.5) is my client's own
responsibility.

Again thank you for the detailed information!

--
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

#22

Tonkuma wrote:

Quote:

Although you already solved the issue by yourself without using join,
I would like to comment on one more thing.
I thought that multiple outer(and/or inner) joins to join more than
two tables would be allowed R:BASE 7.6 or later.

I looked into R:BASE Online Documentation
http://www.rsyntax.com/

and compared each description of OUTER JOIN in the following V/R.
R:BASE Turbo V-8 for Windows
R:BASE 7.6 for Windows
R:BASE 7.5 for Windows
R:BASE 7.1 for Windows
R:BASE 6.5++ for Windows

Following sentence(and INNER JOIN) was inclueded in R:BASE 7.6 and
Turbo V-8, but I couldn't find such statement(and INNER JOIN) in
R:BASE 7.5 or prior.

About JOIN
When you perform a SQL JOIN, ..... You can use multiple joins in the
same SQL statement to query data from as many tables as you like.

Hi Tonkuma,

So it indeed seems the database needs to be upgraded to a more recent
version for multiple joins to work. However, I do not have any control
over this; I'm just programming some queries to the database from
outside, and the database itself (at version 6.5) is my client's own
responsibility.

Again thank you for the detailed information!

--
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

#23

"Carl Colijn" <c.colijn (AT) twologs (DOT) com> wrote

Quote:

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"

#24

"Carl Colijn" <c.colijn (AT) twologs (DOT) com> wrote

Quote:

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"

#25

"Carl Colijn" <c.colijn (AT) twologs (DOT) com> wrote

Quote:

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"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Double join in SQL query - R:Base specific

comp.databases comp.databases

Re: Double join in SQL query - R:Base specific - 06-16-2008 , 02:35 PM

Re: Double join in SQL query - R:Base specific - 06-16-2008 , 02:35 PM

Re: Double join in SQL query - R:Base specific - 06-23-2008 , 09:22 AM

Re: Double join in SQL query - R:Base specific - 06-23-2008 , 09:22 AM

Re: Double join in SQL query - R:Base specific - 06-23-2008 , 09:22 AM