Solved

Convert MS SQL query to MySQL query

Posted on 2014-09-05
11
732 Views
Last Modified: 2014-09-05
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
Comment
Question by:classnet
11 Comments
 

Author Comment

by:classnet
ID: 40305489
FYI... I am using

Software version: 5.5.38-35.2-log - Percona Server (GPL), Release 35.2, Revision 666
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 40305507
seems that you cannot join a sub-query
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40305511
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40305516
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
 

Author Comment

by:classnet
ID: 40305526
"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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40305530
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40305538
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
 

Author Comment

by:classnet
ID: 40305544
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
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 40305556
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
 

Author Closing Comment

by:classnet
ID: 40305595
Not sure how this worked on the other server, but thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40305656
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question