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 )