asked on

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

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

:) too kind Ray! I also adore E-E for what I learn.

Robert Saylor

ASKER

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.

PortletPaul

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

Robert Saylor

ASKER

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.

PortletPaul

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.

Robert Saylor

ASKER

Thanks, I can do that tomorrow. It's public data so nothing sensitive. I will do a refresh on the test database as well.

Robert Saylor

ASKER

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.