Member Avatar for 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 

)
Edited by robotnixon because: n/a
  • 2 Contributors
  • 2 Replies
  • 175 Views
  • 16 Hours Discussion Span
  • Latest Post Latest Post by robotnixon
Member Avatar for 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.

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.