Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 754
  • Last Modified:

Convert MS SQL query to MySQL query

The query below works in MS SQL:

select theEmail, theName, theComment, count(a.theWinner) as theWinnerCount 

from Users left join 

(select  theWeek, case when (awayScore > homeScore) then awayTeam when (homeScore > awayScore) then homeTeam end as theWinner from games) as a 

where Users.theHood = 'pinebrook' and Users.theHood = picks.theHood and picks.theWeek = a.theWeek and a.theWinner = picks.theTeam and picks.theUser = Users.theEmail 

group by theName, picks.theHood, theComment 

order by theWinnerCount desc, theName asc

Open in new window


In MySQL I get the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where Users.theHood = 'pinebrook' and Users.theHood = picks.theHood and picks.th'
0
classnet
Asked:
classnet
1 Solution
 
classnetAuthor Commented:
FYI... I am using

Software version: 5.5.38-35.2-log - Percona Server (GPL), Release 35.2, Revision 666
0
 
Éric MoreauSenior .Net ConsultantCommented:
seems that you cannot join a sub-query
0
 
PortletPaulCommented:
there is no ON for the left join &
there is an alias "picks", with no table

By changing "where" to "ON" I get this far:
SELECT
      theEmail
    , theName
    , theComment
    , COUNT(a.theWinner) AS theWinnerCount

FROM Users
      LEFT JOIN (
                  SELECT
                        theWeek
                      , CASE
                              WHEN (awayScore > homeScore) THEN awayTeam
                              WHEN (homeScore > awayScore) THEN homeTeam END AS theWinner
                  FROM games
            ) AS a

                  ON Users.theHood = 'pinebrook'
                        AND Users.theHood = picks.theHood
                        AND picks.theWeek = a.theWeek
                        AND a.theWinner = picks.theTeam
                        AND picks.theUser = Users.theEmail

GROUP BY
      theName
    , picks.theHood
    , theComment

ORDER BY
      theWinnerCount DESC, theName ASC

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
PortletPaulCommented:
of course MySQL (or Percona) can join subqueries

The problem is pure syntax errors I'm afraid, and I don't know how to solve the missing detail for alias "picks"
0
 
classnetAuthor Commented:
"picks" is a table... as is Users...  These are the two tables involved.  

What you see is the query exactly as it was running on a MS SQL server.
0
 
PortletPaulCommented:
how would I know if it is a table or a subquery?

please put it into your query, where you believe it should be. Then look hard at what was the "where clause"

I could guess if that's what you expect me to do - is that really the intention?
0
 
PortletPaulCommented:
Just to kill of any doubt: that query in the question would not run in mssql at all - sorry. For the same reasons:

there is no ON for the left join &
there is an alias "picks", with no table

Here is my guess:
SELECT
      theEmail
    , theName
    , theComment
    , COUNT(a.theWinner) AS theWinnerCount

FROM Users
      INNER JOIN picks
                  ON Users.theEmail = picks.theUser
                        AND Users.theHood = picks.theHood
      LEFT JOIN (
                  SELECT
                        theWeek
                      , CASE
                              WHEN (awayScore > homeScore) THEN awayTeam
                              WHEN (homeScore > awayScore) THEN homeTeam END AS theWinner
                  FROM games
            ) AS a
                  ON picks.theWeek = a.theWeek
                        AND picks.theTeam = a.theWinner

WHERE Users.theHood = 'pinebrook'

GROUP BY
      theEmail
    , theName
    , theComment
ORDER BY
      theWinnerCount DESC
    , theName ASC
;

Open in new window

0
 
classnetAuthor Commented:
PortletPaul... perhaps you should ignore this question... you sound frustrated.  I simply copied the query exactly as it worked on a MS SQL server...

So, I'd rather you didn't guess or participate at all.
0
 
Dan CraciunIT ConsultantCommented:
If 'picks' is a table, then just add it to the FROM clause.
SELECT
      theEmail
    , theName
    , theComment
    , COUNT(a.theWinner) AS theWinnerCount

FROM Users, picks
      LEFT JOIN (
                  SELECT
                        theWeek
                      , CASE
                              WHEN (awayScore > homeScore) THEN awayTeam
                              WHEN (homeScore > awayScore) THEN homeTeam END AS theWinner
                  FROM games
            ) AS a

                  ON Users.theHood = 'pinebrook'
                        AND Users.theHood = picks.theHood
                        AND picks.theWeek = a.theWeek
                        AND a.theWinner = picks.theTeam
                        AND picks.theUser = Users.theEmail

GROUP BY
      theName
    , picks.theHood
    , theComment

ORDER BY
      theWinnerCount DESC, theName ASC

Open in new window

0
 
classnetAuthor Commented:
Not sure how this worked on the other server, but thanks
0
 
PortletPaulCommented:
The accepted solution has all join conditions in the left join, that isn't correct.

Either fully adopt ANSI join syntax (use my guess above) or revert to the older style (below), don't mix them.
SELECT
      theEmail
    , theName
    , theComment
    , COUNT(a.theWinner) AS theWinnerCount

FROM Users, picks, (
                  SELECT
                        theWeek
                      , CASE
                              WHEN (awayScore > homeScore) THEN awayTeam
                              WHEN (homeScore > awayScore) THEN homeTeam END AS theWinner
                  FROM games
            ) AS a

WHERE Users.theHood = 'pinebrook'
                        AND Users.theHood = picks.theHood
                        AND picks.theWeek = a.theWeek
                        AND a.theWinner = picks.theTeam
                        AND picks.theUser = Users.theEmail

GROUP BY
      theName
    , picks.theHood
    , theComment

ORDER BY
      theWinnerCount DESC, theName ASC
;

Open in new window

as to being grumpy - perhaps that is true but it wasn't with you.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now