mysql left join

I have an SQL query that works fine with mysql 4 but gets error #1054 on mysql 5. I copied the database for testing on an internal server. From my reading looks like the syntax for left join has changed. Can you take a look at my query and guide me?

This is the error:
#1054 - Unknown column 'charters.status_commentID' in 'on clause'

SELECT 

DISTINCT `charters`.`charterID`, 
`charters`.`start_date` `sdate`, 
`charters`.`nights` `days`, 
`charters`.`itinerary`, 
`destinations`.`description` AS desname, 
`status_comments`.`comment` `com1`, 
`status_comments`.`descr` `com1_descr`, 
(`boats`.`charter_rate` + `charters`.`add_on_price` + `charters`.`add_on_price_commissionable`) `rate_range`, 
`boats`.`name` `boatname`, 
`boats`.`name` `boatID`, 
(adddate(`charters`.`start_date`, interval `charters`.`nights` day)) `end_date`, 
count(`inventory`.`inventoryID`) `capacity`, 
count(`inventory`.`status` = 'avail' OR NULL) `bunks` 

FROM 
`boats`, 
`charters`, 
`destinations` 

LEFT JOIN status_comments on charters.status_commentID = status_comments.status_commentID
LEFT JOIN inventory on charters.charterID = inventory.charterID

WHERE 
charters.boatID = boats.boatID 
AND boats.status = 'Active' 
AND charters.destinationID = destinations.destinationID 
AND ((status_comments.comment NOT LIKE 'DRYDOCK%') AND (status_comments.comment NOT LIKE 'DOWN%') AND (status_comments.comment NOT LIKE 'FAM%') or (status_comments.comment IS NULL)) 
AND `charters`.`destinationID` IN (90) 
AND boats.destinationID = '22' 
AND charters.start_date BETWEEN '20130828' AND '20150828' 
AND (charters.statusID <> 7) 

GROUP BY 
charters.charterID 

ORDER BY charters.start_date, boats.name

Open in new window

LVL 7
Robert SaylorSenior DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
it is NOT a good idea to use old pre-ANSI join syntax and ANSI join syntax in the same query.

use 100% ANSI

This is what you have now for joins:
FROM 
`boats`, /* non-ANSI syntax */
`charters`, /* non-ANSI syntax */
`destinations` /* non-ANSI syntax */

LEFT JOIN status_comments on charters.status_commentID = status_comments.status_commentID
LEFT JOIN inventory on charters.charterID = inventory.charterID

WHERE 
charters.boatID = boats.boatID /* non-ANSI join */
AND boats.status = 'Active' 
AND charters.destinationID = destinations.destinationID   /* non-ANSI join */
AND ((status_comments.comment NOT LIKE 'DRYDOCK%') AND (status_comments.comment NOT LIKE 'DOWN%') AND (status_comments.comment NOT LIKE 'FAM%') or (status_comments.comment IS NULL)) 
AND `charters`.`destinationID` IN (90) 
AND boats.destinationID = '22' 
AND charters.start_date BETWEEN '20130828' AND '20150828' 
AND (charters.statusID <> 7) 

Open in new window

You also have created LEFT join but then reference one of those tables through the where clause - this creates the effect of an inner join although you have permitted NULLs. Try this instead
SELECT ....
FROM `boats`
INNER JOIN `charters` ON charters.boatID = boats.boatID
INNER JOIN `destinations` ON charters.destinationID = destinations.destinationID

LEFT JOIN status_comments ON charters.status_commentID = status_comments.status_commentID
                            AND ((status_comments.comment NOT LIKE 'DRYDOCK%') 
                            AND (status_comments.comment NOT LIKE 'DOWN%') 
                            AND (status_comments.comment NOT LIKE 'FAM%')) 

LEFT JOIN inventory on charters.charterID = inventory.charterID

WHERE boats.status = 'Active' 
AND `charters`.`destinationID` IN (90) 
AND boats.destinationID = '22' 
AND charters.start_date BETWEEN '20130828' AND '20150828' 
AND (charters.statusID <> 7) 

Open in new window

by the way, you are grouping and using distinct (this should not be needed). try it without distinct.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
:) too kind Ray! I also adore E-E for what I learn.
0
Robert SaylorSenior DeveloperAuthor Commented:
Any way to make this same query work on both mysql 4 and mysql 5?

Main reason, my developer server is php 5.3.x with mysql 5.x but my production system is php 5.2.x with mysql 4.

We have a very large program that must be on mysql 4. It won't work on 5. Eventually I have to rewrite it but that is way down the road. But for a CMS I am writing it works with some of the data from the legacy system and would like to be able to use a query that works with 4 and 5.

I did run the new query and that does work on 5 but did not give me the same results as the mysql 4 server fyi.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

PortletPaulfreelancerCommented:
what are the exact version number of 4?
(versions matter) might as well do this for 5 also

e.g. "Starting from 4.0.14, for a LEFT JOIN , if the WHERE condition is always false for the generated NULL row, the LEFT JOIN is changed to a normal join." http://dev.mysql.com/doc/refman/4.1/en/left-join-optimization.html
0
Robert SaylorSenior DeveloperAuthor Commented:
Production Server (it's a dedicated mysql server)
4.1.22-standard-log

Development environment:
Your MySQL connection id is 688
Server version: 5.6.12 Source distribution

My goal is to eventually migrate things to MySQL 5 but that will take a lot of time.
0
PortletPaulfreelancerCommented:
ok, next task I'm afraid

can you provide some output rows that display a difference in results?
(I'm assuming the data is "the same" in both databases)

I don't have MySQL 4 so it's going to be a little hard for me to assist, samples may give me some clues.
0
Robert SaylorSenior DeveloperAuthor Commented:
Thanks, I can do that tomorrow. It's public data so nothing sensitive. I will do a refresh on the test database as well.
0
Robert SaylorSenior DeveloperAuthor Commented:
Thanks for your help. I will revisit this again when I have more time. I copied my development code to the production server and completing the code there so I can access the mysql 4 server.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.