Serious problem (bug?) : LEFT JOIN acting like INNER JOIN

Hi

+ I have M$ Access ('97!) with linked tables via MyODBC
(3.51.12) to MySQL 5.0.22.

+ I have a "people" table.

+ I have a "mailingHistory" table, which records which people
have had which mailings.

+ I have a query saved in Access called XrefMailing123 which
simply filters the mailingHistory table for the mailing with
id 123.

+ I have a search query that **should** return all the people
who have NOT had this particular mailing. This query uses the
people table, LEFT JOINs the XrefMailing123 query and has a
WHERE for XrefMailing123.pid IS NULL. This query returns NO
rows.

I know the SQL is fine, because if I move all the tables into
Access, it works fine. Likewise I can use the MySQL command
line with the same and it works (obviously this test requires
that I create a "view" for the subquery -- works fine, also
works if I put the sql for the subquery in directly).

The SQL for the XrefMailing123 query is:
SELECT id, pid FROM mailingHistory WHERE mlid=123;

The SQL for the search query is:
SELECT people.id, XrefMailing123.id FROM people LEFT JOIN
XrefMailing123 ON people.id=XrefMailing123.pid WHERE
XrefMailing123.pid IS NULL;

If I remove the "IS NULL" from the criteria, I can see that the
search query is behaving as though I'd asked for an INNER JOIN,
not a LEFT JOIN.

Can anyone help?


--
Rich Lott


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

Re: Serious problem (bug?) : LEFT JOIN acting like INNER JOIN

This may be an Access97 bug as it seems to work as expected in Access 2000.

I'd still be interested to know if anyone else has had experience with
this because I need to run it on a mixed network of '97 and 2000 machines.

thanks,

--
Rich Lott


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

RE: Serious problem (bug?) : LEFT JOIN acting like INNER JOIN

I have had troubles with a similar situation too.

If you have all your tables in MySQL anyway, why not do it all in MySQL
with a pass-thru query?

SELECT p.id
FROM people AS p LEFT JOIN (
SELECT pid=20
FROM mailingHistory=20
WHERE mild =3D 123
) AS m on p.id =3D m.pid
WHERE m.pid IS NULL

The version of MySQL you are running should handle this OK.

John B.

-----Original Message-----
From: Rich Lott - People & Planet [mailto:rich.lott@peopleandplanet.org]

Sent: Saturday, 8 July 2006 12:39 AM
To: myodbc@lists.mysql.com
Subject: Serious problem (bug?) : LEFT JOIN acting like INNER JOIN

Hi

+ I have M$ Access ('97!) with linked tables via MyODBC
(3.51.12) to MySQL 5.0.22.

+ I have a "people" table.

+ I have a "mailingHistory" table, which records which people
have had which mailings.

+ I have a query saved in Access called XrefMailing123 which
simply filters the mailingHistory table for the mailing with
id 123.

+ I have a search query that **should** return all the people
who have NOT had this particular mailing. This query uses the
people table, LEFT JOINs the XrefMailing123 query and has a
WHERE for XrefMailing123.pid IS NULL. This query returns NO
rows.

I know the SQL is fine, because if I move all the tables into
Access, it works fine. Likewise I can use the MySQL command
line with the same and it works (obviously this test requires
that I create a "view" for the subquery -- works fine, also
works if I put the sql for the subquery in directly).

The SQL for the XrefMailing123 query is:
SELECT id, pid FROM mailingHistory WHERE mlid=3D123;

The SQL for the search query is:
SELECT people.id, XrefMailing123.id FROM people LEFT JOIN
XrefMailing123 ON people.id=3DXrefMailing123.pid WHERE
XrefMailing123.pid IS NULL;

If I remove the "IS NULL" from the criteria, I can see that the
search query is behaving as though I'd asked for an INNER JOIN,
not a LEFT JOIN.

Can anyone help?


--
Rich Lott



--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg

Re: Serious problem (bug?) : LEFT JOIN acting like INNER JOIN

jbonnett@sola.com.au wrote:
> I have had troubles with a similar situation too.
>
> If you have all your tables in MySQL anyway, why not do it all in MySQL
> with a pass-thru query?

Because I have a rather extensive front-end with SQL embedded everywhere,
including in over 100,000 lines of VBA code.

But, with a sigh, this is the only solution I can see. At least there will
be speed and other benefits to gain from it, too. But it still stings
because I allocated a week for the migration and it's going to take a lot
longer now.

thanks,

--
rich

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org