 | | | Serious problem (bug?) : LEFT JOIN acting like INNER JOIN | Serious problem (bug?) : LEFT JOIN acting like INNER JOIN 2006-07-09 - By jbonnett@(protected)
Back
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
FROM mailingHistory
WHERE mild = 123
) AS m on p.id = 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@(protected)]
Sent: Saturday, 8 July 2006 12:39 AM
To: myodbc@(protected)
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 mlid3;
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)
|
|
|