Double join in SQL query - R:Base specific

#1

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

#2

Carl Colijn 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?

And to add some information: it's an R:Base 2000 v6.5 database.

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

#3

Carl Colijn 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?

And to add some information: it's an R:Base 2000 v6.5 database.

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

#4

Carl Colijn 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?

And to add some information: it's an R:Base 2000 v6.5 database.

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

#5

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

Does R:Base even claim to do outer joins at all? (Can you do it with even
just two tables?)

If it doesn't, then you might be able to do it (albeit with worse
performance probably) by writing it as a union:

select ...
from a, b
where a.p = b.p
union all
select ...
from a
where not exists ( select * from b where a.p = b.p)

Obviously this is a two-table outer join. I can't summon the energy to
write out a three table outer join in this way.

Roy

#6

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

Does R:Base even claim to do outer joins at all? (Can you do it with even
just two tables?)

If it doesn't, then you might be able to do it (albeit with worse
performance probably) by writing it as a union:

select ...
from a, b
where a.p = b.p
union all
select ...
from a
where not exists ( select * from b where a.p = b.p)

Obviously this is a two-table outer join. I can't summon the energy to
write out a three table outer join in this way.

Roy

#7

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

Does R:Base even claim to do outer joins at all? (Can you do it with even
just two tables?)

If it doesn't, then you might be able to do it (albeit with worse
performance probably) by writing it as a union:

select ...
from a, b
where a.p = b.p
union all
select ...
from a
where not exists ( select * from b where a.p = b.p)

Obviously this is a two-table outer join. I can't summon the energy to
write out a three table outer join in this way.

Roy

#8

Roy Hann wrote:

Quote:

"Carl Colijn" <c.colijn (AT) twologs (DOT) com> wrote in message
news:xn0frcbrs13ke0v000 (AT) news (DOT) upc.nl...
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.

Does R:Base even claim to do outer joins at all? (Can you do it with
even just two tables?)

If it doesn't, then you might be able to do it (albeit with worse
performance probably) by writing it as a union:

select ...
from a, b
where a.p = b.p
union all
select ...
from a
where not exists ( select * from b where a.p = b.p)

Obviously this is a two-table outer join. I can't summon the energy
to write out a three table outer join in this way.

Roy

Hi Roy,

Thank you for your answer!

R:Base is indeed capable of performing outer joins (left, right and
full). One join between two tables isn't a problem, but two joins at
once doesn't seem to be possible.

Anyway, if the posibility to combine two outer left joins in one select
query doesn't exist in R:Base, I'll have to use a workaround like you
suggested. The query will not run frequently, and when it does it only
selects one record from table a, so performance isn't that big of an
issue (but aesthetics is

)

Further suggestions always welcome!

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

#9

Roy Hann wrote:

Quote:

"Carl Colijn" <c.colijn (AT) twologs (DOT) com> wrote in message
news:xn0frcbrs13ke0v000 (AT) news (DOT) upc.nl...
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.

Does R:Base even claim to do outer joins at all? (Can you do it with
even just two tables?)

If it doesn't, then you might be able to do it (albeit with worse
performance probably) by writing it as a union:

select ...
from a, b
where a.p = b.p
union all
select ...
from a
where not exists ( select * from b where a.p = b.p)

Obviously this is a two-table outer join. I can't summon the energy
to write out a three table outer join in this way.

Roy

Hi Roy,

Thank you for your answer!

R:Base is indeed capable of performing outer joins (left, right and
full). One join between two tables isn't a problem, but two joins at
once doesn't seem to be possible.

Anyway, if the posibility to combine two outer left joins in one select
query doesn't exist in R:Base, I'll have to use a workaround like you
suggested. The query will not run frequently, and when it does it only
selects one record from table a, so performance isn't that big of an
issue (but aesthetics is

)

Further suggestions always welcome!

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

#10

Roy Hann wrote:

Quote:

"Carl Colijn" <c.colijn (AT) twologs (DOT) com> wrote in message
news:xn0frcbrs13ke0v000 (AT) news (DOT) upc.nl...
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.

Does R:Base even claim to do outer joins at all? (Can you do it with
even just two tables?)

If it doesn't, then you might be able to do it (albeit with worse
performance probably) by writing it as a union:

select ...
from a, b
where a.p = b.p
union all
select ...
from a
where not exists ( select * from b where a.p = b.p)

Obviously this is a two-table outer join. I can't summon the energy
to write out a three table outer join in this way.

Roy

Hi Roy,

Thank you for your answer!

R:Base is indeed capable of performing outer joins (left, right and
full). One join between two tables isn't a problem, but two joins at
once doesn't seem to be possible.

Anyway, if the posibility to combine two outer left joins in one select
query doesn't exist in R:Base, I'll have to use a workaround like you
suggested. The query will not run frequently, and when it does it only
selects one record from table a, so performance isn't that big of an
issue (but aesthetics is

)

Further suggestions always welcome!

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