(Fwd) Re: MS Access, outer join and ODBC
------- Forwarded Message Follows -------
I am trying to join two tables, TABLE1 and TABLE2, with MS-Access
ver. 2.0 for Windows 3.11.
The tables are stored in an Informix database; I am using
Informix CLI (ver. 2.5) as ODBC driver to access the tables.
In particulary, I am interested to list all the records in TABLE1 and
only the records in TABLE2 with TABLE1.FK = TABLE2.ID (outer join).
TABLE1 TABLE2
+--------------+ +-------------+
| PK | +-----> | ID |
+--------------+ | +-------------+
| NAME | | | DESCRIPTION |
+--------------+ | +-------------+
| FK | ---+
+--------------+
PK is TABLE1's primary key, ID is TABLE2's primary key.
TABLE1 has a foreign key: FK.
I have created a query (QUERY1) like the following:
SELECT DISTINCTROW table1.name, table2.description
FROM table1 LEFT JOIN table2 ON table1.fk = table2.id;
In this case, MS-Access generates the following ODBC call:
SQLExecDirect:
SELECT table2.id,table1.pk
FROM {oj table1 LEFT OUTER JOIN table2
ON (table1.fk = table2.id ) }
ORDER BY table2.fk
and I get the following error:
[INTERSOLV][ODBC Informix Driver][Informix]
ORDER BY column (fk) must be in SELECT list. (#-309)
If I change the query as following (QUERY2):
SELECT DISTINCTROW table1.name, table2.description
FROM table2 RIGHT JOIN table1 ON table2.id = table1.fk;
MS-Access generates the following ODBC calls:
SQLExecDirect:
SELECT table2.id,table1.pk
FROM {oj table1 LEFT OUTER JOIN table2
ON (table1.fk = table2.id ) }
ORDER BY table1.id
SQLExecDirect:
SELECT table2.id,table2.description,table1.pk,
table1.name,table1.fk
FROM {oj table1 LEFT OUTER JOIN table2
ON (table1.fk = table2.id ) }
ORDER BY table1.id
etc....
and I get the correct result.
I think the two queries, QUERY1 and QUERY2, should give the same
result.
Why, in case of QUERY1, MS-Access try to sort the row using a column
not in the select list ?
Has anyone any suggestion about this problem ?
Thank you in advance.
Mariuccia Venini
Ospedale San Raffaele
tel 2643-4048
venini@rsisi.hsr.it