MS SQL to mysql syntax

[robotnixon]

Hey everyone.

I have a mysql project that requires only some basic queries except one. Nothing more challenging than some really basic select/insert statements. No problem there. But the one complex one isn't working out.

I typically work a little with SQL server and wrote the following at work in SQL management studio. The code works fine there, but gets several syntax errors when I run it in phpmyadmin.

If anyone can give me tips on how to convert it over I'd appreciate it. It checks a user's lists of movies, finds someone else who likes at least 2 of the same films and then shows the first user a list of the second user's films. Sort of a suggestion system based on similar users.

DECLARE @UserName AS VARCHAR(50) 
DECLARE @MinMatch AS INT
SET @UserName = 'John' 
SET @MinMatch = 2

SELECT
Title

FROM
Movies INNER JOIN
(
                SELECT

                UserName,
                Count(Title) AS [Match]
                FROM Movies

                WHERE
                Title IN
                (
                Select
                Title
                
                FROM
                Users INNER JOIN Movies ON Users.UserName=Movies.UserName

                WHERE

                Users.UserName = @UserName 
                )
                AND UserName <> @UserName  
                GROUP BY UserName
                HAVING

                Count(Title) >= @MinMatch

) AS Recommend ON Movies.UserName = Recommend.UserName

WHERE
Title NOT IN
(
                SELECT
                Title

                FROM
                Users INNER JOIN Movies ON Users.UserName=Movies.UserName

                WHERE
                Users.UserName = @UserName 

)

[smantscheff]

What syntax errors do you get? Do you get them also from the command line MySQL client? And do they persist if you delimit your statements with semicolons?
Also I think you have to add an alias clause to every sub-select which uses the same tables as the main select, so you need one table alias name for each FROM clause except for the outermost.

[robotnixon]

Thanks smantscheff.

I figured it out. Just needed to remove the first 2 lines, add semicolons to the next two, and remove the AS [MATCH] from line 16.