Convert sp from MS SQL to MySQL (5.0)

Can anyone help me convert a stored procedure  that includes a CTE from MS SQL to MySQL 5.0?  
WITH CompanyListings AS 
	(
	SELECT
		T1.ComanyID, T1.CompanyName, T1.CompanyURL, T1.School, T2.DisplayAlways, 
		MAX(T3.SessionEndDate) OVER (PARTITION BY T1.ComanyID) AS DisplayDate
	FROM TrainingCompanies T1
	LEFT JOIN TrainingCourses T2 ON T1.ComanyID = T2.Course_CompanyID
	LEFT JOIN TrainingSessions T3 ON T2.CourseID = T3.Session_CourseID	
	)
SELECT DISTINCT 
	ComanyID, CompanyName, CompanyURL, ISNULL(School,'') AS School 
FROM CompanyListings
WHERE DisplayDate > DATEADD(D,-3, GETDATE()) OR DisplayAlways = 1
ORDER BY CompanyName

Open in new window

cgcmqAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Aaron ShiloChief Database ArchitectCommented:
SELECT DISTINCT 
	ComanyID, CompanyName, CompanyURL, ISNULL(School,'') AS School 
FROM (
	SELECT
		T1.ComanyID, T1.CompanyName, T1.CompanyURL, T1.School, T2.DisplayAlways, 
		MAX(T3.SessionEndDate) OVER (PARTITION BY T1.ComanyID) AS DisplayDate
	FROM TrainingCompanies T1
	LEFT JOIN TrainingCourses T2 ON T1.ComanyID = T2.Course_CompanyID
	LEFT JOIN TrainingSessions T3 ON T2.CourseID = T3.Session_CourseID	
	) CompanyListings 
WHERE DisplayDate > DATEADD(D,-3, GETDATE()) OR DisplayAlways = 1
ORDER BY CompanyName 

Open in new window

0
cgcmqAuthor Commented:
Are you certain that your solution is MySQL?  It looks like an MS SQL query.
0
Aaron ShiloChief Database ArchitectCommented:
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

cgcmqAuthor Commented:
Sorry - I don't work with MySQL much & do not know the query syntax very well.  When I cut & pasted the query & ran it (using Navicat) I received the following error:

1064 - You have an error in your SQL syntax... near '') AS School
FROM (
      SELECT
0
Aaron ShiloChief Database ArchitectCommented:
try this


SELECT DISTINCT 
	ComanyID, CompanyName, CompanyURL, IfNULL(School,'') AS School 
FROM (
	SELECT
		T1.ComanyID, T1.CompanyName, T1.CompanyURL, T1.School, T2.DisplayAlways, 
		MAX(T3.SessionEndDate) OVER (PARTITION BY T1.ComanyID) AS DisplayDate
	FROM TrainingCompanies T1
	LEFT JOIN TrainingCourses T2 ON T1.ComanyID = T2.Course_CompanyID
	LEFT JOIN TrainingSessions T3 ON T2.CourseID = T3.Session_CourseID	
	) CompanyListings 
WHERE DisplayDate > DATEADD(D,-3, CURDATE())OR DisplayAlways = 1
ORDER BY CompanyName  

Open in new window

0
cgcmqAuthor Commented:
That solved the initial error - thank you.  

It now stumbles at:
  (PARTITION BY T1.ComanyID) AS DisplayDate
      FROM TrainingCompanies T1
0
Aaron ShiloChief Database ArchitectCommented:
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
cgcmqAuthor Commented:
Like I said, I am not familiar with MySQL so it is going to take me some time to go through this.  I will get back to you shortly.
0
cgcmqAuthor Commented:
I downloaded a syntax converter (SwissSQL Console) and it gave me an output very similar to what you had which was of limited use as it clearly is not functional MySQL.

Instead of following the example at Xaprb, I re-wrote the query in MS SQL so that it did not use a CTE or 'partition over'.  From that I was able to more easily convert it to MySQL.

      SELECT DISTINCT
                   ComanyID,
                   CompanyName,
                   CompanyURL,
                   COALESCE(School, '') AS School
      FROM TrainingCompanies T1
      LEFT JOIN TrainingCourses T2 ON T1.ComanyID = T2.Course_CompanyID
      WHERE CourseID IN
            (
            SELECT Session_CourseID
            FROM TrainingSessions
            WHERE SessionEndDate > DATE_ADD((CURRENT_TIMESTAMP), INTERVAL  - 3  DAY)
            ) OR DisplayAlways  = 1

ORDER BY CompanyName
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.