Access query syntax vs MySQL query syntax 3

[bubarooni]

I have made the leap to MySQL and am currently trying to move a vb 6 app from Access to MySQL. I used the MySQL Migration Toolkit to move the Access db to MySQL.

I am struggling with some of my queries in the application I made in VB 6 though.

The following query worked perfectly in MS Access:

SELECT InjectSchedule.isChartNo AS Chart_Number, InjectSchedule.isStartDate, Patients.pLName & ', ' &  Patients.pFName AS Patient_Name, 
Doctors.mdLName & ', ' &  Doctors.mdFName AS Doctor_Name, Insurance.iInsCompany AS Insurance, HHCA.hName AS HHCA
FROM Patients INNER JOIN (Insurance INNER JOIN (HHCA INNER JOIN (Doctors INNER JOIN InjectSchedule ON Doctors.mdID =InjectSchedule.isDoctor) ON HHCA.hID = InjectSchedule.isHHCA) ON Insurance.iID= InjectSchedule.isIns) ON Patients.pID = InjectSchedule.isPatID
WHERE InjectSchedule.isInactive = False 
AND InjectSchedule.isTherComp = False 
ORDER BY Patients.pLName"

In MySQL the alias for fields produces nothing and having the two conditions in the where clause prevents any rows from returning.

If I take out the aliases by pulling in the fields directly (i.e. no "Patients.pLName & ', ' & Patients.pFName AS Patient_Name" just "Patients.pFName, Patients.pLName") I get the pertinent fields returned.

If I use either of the where conditions seperately I get rows retruned.

Is the syntax that different? I'm googling for answers but haven't found a thing yet. If you can spot an obvious error and/or have some good links on this topic I'd sure appreciate either.

Thanks In Advance

 

[TonyGroves]

The syntax for string concatenation in MySQL is:[tt]
CONCAT(a,b,c)[/tt]

There may be a difference in interpretation of "FALSE" between Access and MySQL. In MYSQL, NULL=FALSE produces NULL.

You also don't need to nest all those inner joins using brackets. You can simple chain them together.

 

[bubarooni]

Thanks for the tips!

I used the CONCAT_WS form to get the patients and doctors name to display correctly. Pointing me to the NULL=False thing got me to look at the values in the database. They are stored as 0's and 1's. I think 0 = yes and 1 = false though I need to dig that up somewhere and confirm it.

Now, for that 'nesting inner join' thing. While I said that the query worked fine in Access some of my queries are preposterously slow. Could this nesting be why? How do I 'chain' them together?

 

[azzazzello]

I think if you index the fields you are joining on, you will see a marked improvement in return times

 

[TonyGroves]

It's not so much a speed thing, more to do with readability. You could instead use something like:
[tt]
FROM
InjectSchedule
INNER JOIN Patients
ON Patients.pID = InjectSchedule.isPatID
INNER JOIN Insurance
ON Insurance.iID= InjectSchedule.isIns
INNER JOIN HHCA
ON HHCA.hID = InjectSchedule.isHHCA
INNER JOIN Doctors
ON Doctors.mdID =InjectSchedule.isDoctor
[/tt]
Although the MySQL optimiser is very intelligent, it might be possible to improve speed by tweaking the order of the joins (putting the joins that produce the smallest result set first), assuming of course that the join fields are already indexed, as Azzazzello pointed out,

 

[bubarooni]

I am using MySQL Administrator 1.0.2b. When I look in there I see isChartNo listed as an index and I see isPatID listed as an index but not isIns, isHHCA and isDoctor.

In Access,isChartNo was the Primary Key which I assume is an index in MySQL parlance. Did the MySQL Migration Toolkit make the isPatID an index too?

I don't see a way in the MySQL Adminstrator to set isIns, isHHCA and isDoctor as indices. Is that a command line function only?

 

[azzazzello]

http://dev.mysql.com/doc/mysql/en/create-index.html

 

[bubarooni]

I was having trouble with the command line so I set the additional indices in the original Access db and then reran the SQL Migration Toolkit and overwrote the MySQL db. Worked perfect and the query runs in a third of the time.

One thing I've noticed as I've been working on the vb coding is that my Access Yes/No fields have been changed to 'tinyint(1) unsigned' datatypes. My True/False coding on checkboxes doesn't work anymore.

False seems to save as '0' but true seems to save as '255'. Does that sound right? I'm manually changing the coding from 'True' to '1' and 'False' to '0' just to make it work but am curious about this.

Thanks Again

 

[TonyGroves]

TRUE is 1 and FALSE is 0.
See

http://dev.mysql.com/doc/mysql/en/boolean-values.html

.
Before version 4.1, TRUE and FALSE were not defined.

 

[bubarooni]

Thanks for the response. TRUE is definitely being saved as 255 for some reason. I'm gonna just plow on thru it by recoding TRUE to '1' as I'm kinda pressed for time. I'll revisit it a later date.

I've got a feeling I'm gonna get pretty familiar with that online reference manual.

Thanks Again for your help guys!