MS Access and MySQL difference

[Anonymous]

Howdy. The setup I am currently working on using is using Microsoft Access as the front end, but having the tables stored in a MySQL database. I'm using this setup because I have vb code along with other things in the access database, but I am trying to get away from having any actual data in the access database. I'm using the mysql odbc driver to link the tables to the access database.

All this I have done with no problem. The part I am running into problems with is some differences in SQL. I have the following statement:

SELECT *
FROM ((records 
LEFT JOIN address ON (records.id = address.id And address.cd = 'PM')) 
LEFT JOIN name ON name.id = student.id
WHERE records.importdate = 'some date';

For the above, I expect it to only return back 1 record. The 1 record has 2 entries in the address table though, and the address.cd are PM and ON for those entries. I am wanting to only get the PM address entry.

In MySQL it works great. It returns back one record and the only address that is returned back in the PM address.

In Access, it returns back 2 records, one with the PM info and the other with the ON info. From what I can tell, it is basically ignoring the address.cd='PM' and left joining to all the records in the address table. Since there are two records in the address table, it returns back 2 records. It works if I put the address.cd=PM in the WHERE clause, but this will exclude records if they do not have a PM address, which I do not want to do.

Any suggestions on how to get it to work? Is the address.cd='PM' part of the join only a MySQL specific function? I know in Access you can have field=field in the ON part of the join, but I'm not sure if having field=constant is MySQL specific.

Thanks

 

[gesf]

... Is the address.cd='PM' part of the join only a MySQL specific function? ...

Really don´t know, but... Don´t you need to close some bracket ' ) ' !?

 

[Anonymous]

It is ok in my db, I just mistyped it on the forum post:

SELECT *
FROM (records
LEFT JOIN address ON (records.id = address.id And address.cd = 'PM'))
LEFT JOIN name ON name.id = student.id
WHERE records.importdate = 'some date';

Here is a workaround. It works if you use it within Access when interacting w/ a MySQL database, but it does not work directly within MySQL

SELECT *
FROM (records
LEFT JOIN (SELECT * FROM address WHERE address.cd = 'PM') address ON records.id = address.id)
LEFT JOIN name ON name.id = student.id
WHERE records.importdate = 'some date';

It is wierd because if you put a subselect, ie..WHERE something In (select something from somewhere), into Access that has linked tables to MySQL it will complain that MySQL does not support it. The above seems to work, yet MySQL does not seem to support it. *shakes head*

 

[gesf]

Hunn... I´m not sure about that address table name in the in the LEFT JOIN line (try to remove it). So you´ll have:

SELECT * 
FROM (records 
LEFT JOIN (SELECT * FROM address WHERE address.cd = 'PM') ON records.id = address.id) 
LEFT JOIN name ON name.id = student.id 
WHERE records.importdate = 'some date';

BTW: And maybe you don´t need to refer the table name in the select!
So try:

(SELECT * FROM address WHERE cd = 'PM')