 | | | Serious problem (bug?) : LEFT JOIN acting like INNER JOIN | Serious problem (bug?) : LEFT JOIN acting like INNER JOIN 2006-07-07 - By Rich Lott - People & Planet
Back
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=mysql@(protected)
|
|
|