--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 12:11 PM 14/10/2003 +0000, you wrote:
> >Hi all,
> >
> >I have the following problem:
> >
> >Tables in Firebird DB:
> >Address (Name varchar(50), CountryID integer, StatusID integer)
> >Country (ID integer, CountryName varchar(50))
> >Status (ID integer, StatusName varchar(50))
> >
> >A address may (but must not) have a CountryID and a StatusID.
> >So the table Address could look like this:
> >
> >Name | CountryID | StatusID
> >---------------------------
> >a1 | 1| 5
> >a2 | NULL| NULL
> >a3 | NULL| 1
> >a4 | 2| 1
> >a5 | NULL| NULL
> >
> >
> >All tables are filled with data.
> >
> >All tables are linked to an MS Access DB via ODBC.
> >
> >When I creat a query in MS Access which combines the three
> >tables I get unexpected results, because I get data from
> >the tables Country and Status linked to rows of Address
> >where CoutryID or StatusID is NULL:
> >
> >Name | CountryName | StatusName
> >-------------------------------
> >a1 | c1| s5
> >a2 | NULL| s5
> >a3 | NULL| s5
> >a4 | c2| s1
> >a5 | NULL| s1
> >
> >When I import the data into my Access DB, the the query
> >works fine.
> >
> >I have tried this with the Gemini ODBC Driver 2.1 and
> >the Easy Soft Interbase 6 ODBC driver. The result is
> >the same....
> >
> >Firebird: 1.5 RC6
> >Access: 2002 SP2
> >
> >What am I doing wrong?
> >
> >Any ideas or hints?
>
> No. Tell us what the query is.

The query in MS Access is:

SELECT ADDRESS.NAME, STATUS.STATUSNAME, COUNTRY.COUNTRYNAME
FROM (ADDRESS LEFT JOIN STATUS ON ADDRESS.STATUSID = STATUS.ID) LEFT
JOIN COUNTRY ON ADDRESS.COUNTRYID = COUNTRY.ID;

Regards

Jurgen