Nesting outer joins with ODBC and Access 2003

[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

 

[Bill Mosca]

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

 

[marco.dibartolomeo]

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

 

[Bill Mosca]

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