Nesting outer joins with ODBC and Access 2003

  • Thread starter Thread starter marco.dibartolomeo
  • Start date Start date
M

marco.dibartolomeo

Hello everybody,

I have this test database (sql textual syntax):

CREATE TABLE Table1 (
id int,
value varchar(255)
);

CREATE TABLE Table2 (
id int,
value varchar(255)
);

CREATE TABLE Table1Table2 (
id1 int,
id2 int
);

INSERT INTO Table1 VALUES (1, 'A');
INSERT INTO Table1 VALUES (2, 'B');
INSERT INTO Table1 VALUES (3, 'C');
INSERT INTO Table2 VALUES (4, 'a');
INSERT INTO Table2 VALUES (5, 'b');
INSERT INTO Table2 VALUES (6, 'c');
INSERT INTO Table1Table2 VALUES (1, 4);
INSERT INTO Table1Table2 VALUES (1, 5);
INSERT INTO Table1Table2 VALUES (1, 6);
INSERT INTO Table1Table2 VALUES (2, 4);
INSERT INTO Table1Table2 VALUES (2, 5);

Then I try to select data with the query:
(ODBC syntax, I'm coding in c++)

SELECT T1.*,T2.* FROM
{oj Table1 T1 LEFT OUTER JOIN (Table1Table2 T1T2 LEFT OUTER JOIN
Table2 T2 on T2.id=T1T2.id2) on T1.id=T1T2.id1}

Expected result:

1 A 4 a
1 A 5 b
1 A 6 c
2 B 4 a
2 B 5 b
3 C

Actual result (with Access 2003):

[Microsoft][Driver ODBC Microsoft Access] Errore riservato (|); non ci
sono messaggi per questo errore.
(It's italian, it says: "Reserved Error (|); there aren't any messages
for this error".

Why? What am I doing wrong? The query should be correct, MySQL (via
ODBC) give the expected result without complaining, the paste above is
the mysql output indeed.

Hoping for hep,
greetings
 
I'm not familiar with MySQL syntax, but you might try a more ANSI compliant
code like:
SELECT T1.*,T2.*
FROM Table1 T1 LEFT JOIN (Table1Table2 T1T2 LEFT JOIN
Table2 T2 on T2.id=T1T2.id2) on T1.id=T1T2.id1

Notice I removed the curly brackets, the word OUTER and the oj
 
Thank you for the reply!
That syntax isn't about mysql: it's the ODBC way to do an outer join,
it's the same with Access.
Unfortunately, I realized too late that Access' ODBC driver doesn't
support nested outer joins, that's why it wasn't working. MSDN states
so, and a call to SQLGetInfo api function seems to confirm it. What a
pity!

I'm not familiar with MySQL syntax, but you might try a more ANSI compliant
code like:
SELECT T1.*,T2.*
FROM Table1 T1 LEFT JOIN (Table1Table2 T1T2 LEFT JOIN
Table2 T2 on T2.id=T1T2.id2) on T1.id=T1T2.id1

Notice I removed the curly brackets, the word OUTER and the oj

--
Bill Mosca, MS Access MVPhttp://www.thatlldoit.comhttp://mvp.suppor...roups.yahoo.com/group/MS_Access_Professionals




Hello everybody,
I have this test database (sql textual syntax):
CREATE TABLE Table1 (
 id int,
 value varchar(255)
);
CREATE TABLE Table2 (
 id int,
 value varchar(255)
);
CREATE TABLE Table1Table2 (
 id1 int,
 id2 int
);
INSERT INTO Table1 VALUES (1, 'A');
INSERT INTO Table1 VALUES (2, 'B');
INSERT INTO Table1 VALUES (3, 'C');
INSERT INTO Table2 VALUES (4, 'a');
INSERT INTO Table2 VALUES (5, 'b');
INSERT INTO Table2 VALUES (6, 'c');
INSERT INTO Table1Table2 VALUES (1, 4);
INSERT INTO Table1Table2 VALUES (1, 5);
INSERT INTO Table1Table2 VALUES (1, 6);
INSERT INTO Table1Table2 VALUES (2, 4);
INSERT INTO Table1Table2 VALUES (2, 5);
Then I try to select data with the query:
(ODBC syntax, I'm coding in c++)
SELECT T1.*,T2.* FROM
{oj Table1 T1 LEFT OUTER JOIN (Table1Table2 T1T2 LEFT OUTER JOIN
Table2 T2 on T2.id=T1T2.id2) on T1.id=T1T2.id1}
Expected result:
1 A 4 a
1 A 5 b
1 A 6 c
2 B 4 a
2 B 5 b
3 C
Actual result (with Access 2003):
[Microsoft][Driver ODBC Microsoft Access] Errore riservato (|); non ci
sono messaggi per questo errore.
(It's italian, it says: "Reserved Error (|); there aren't any messages
for this error".
Why? What am I doing wrong? The query should be correct, MySQL (via
ODBC) give the expected result without complaining, the paste above is
the mysql output indeed.
Hoping for hep,
greetings
 
Ah, I see. ODBC in a query is a new one on me.

--
Bill Mosca, MS Access MVP
http://www.thatlldoit.com
http://mvp.support.microsoft.com/profile/Bill.Mosca
http://tech.groups.yahoo.com/group/MS_Access_Professionals




Thank you for the reply!
That syntax isn't about mysql: it's the ODBC way to do an outer join,
it's the same with Access.
Unfortunately, I realized too late that Access' ODBC driver doesn't
support nested outer joins, that's why it wasn't working. MSDN states
so, and a call to SQLGetInfo api function seems to confirm it. What a
pity!

I'm not familiar with MySQL syntax, but you might try a more ANSI
compliant
code like:
SELECT T1.*,T2.*
FROM Table1 T1 LEFT JOIN (Table1Table2 T1T2 LEFT JOIN
Table2 T2 on T2.id=T1T2.id2) on T1.id=T1T2.id1

Notice I removed the curly brackets, the word OUTER and the oj

--
Bill Mosca, MS Access
MVPhttp://www.thatlldoit.comhttp://mvp.suppor...roups.yahoo.com/group/MS_Access_Professionals




Hello everybody,
I have this test database (sql textual syntax):
CREATE TABLE Table1 (
id int,
value varchar(255)
);
CREATE TABLE Table2 (
id int,
value varchar(255)
);
CREATE TABLE Table1Table2 (
id1 int,
id2 int
);
INSERT INTO Table1 VALUES (1, 'A');
INSERT INTO Table1 VALUES (2, 'B');
INSERT INTO Table1 VALUES (3, 'C');
INSERT INTO Table2 VALUES (4, 'a');
INSERT INTO Table2 VALUES (5, 'b');
INSERT INTO Table2 VALUES (6, 'c');
INSERT INTO Table1Table2 VALUES (1, 4);
INSERT INTO Table1Table2 VALUES (1, 5);
INSERT INTO Table1Table2 VALUES (1, 6);
INSERT INTO Table1Table2 VALUES (2, 4);
INSERT INTO Table1Table2 VALUES (2, 5);
Then I try to select data with the query:
(ODBC syntax, I'm coding in c++)
SELECT T1.*,T2.* FROM
{oj Table1 T1 LEFT OUTER JOIN (Table1Table2 T1T2 LEFT OUTER JOIN
Table2 T2 on T2.id=T1T2.id2) on T1.id=T1T2.id1}
Expected result:
1 A 4 a
1 A 5 b
1 A 6 c
2 B 4 a
2 B 5 b
3 C
Actual result (with Access 2003):
[Microsoft][Driver ODBC Microsoft Access] Errore riservato (|); non ci
sono messaggi per questo errore.
(It's italian, it says: "Reserved Error (|); there aren't any messages
for this error".
Why? What am I doing wrong? The query should be correct, MySQL (via
ODBC) give the expected result without complaining, the paste above is
the mysql output indeed.
Hoping for hep,
greetings
 
Back
Top