Solved

Convert MS SQL query to MySQL query

Posted on 2014-09-05
11
690 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 69

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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to loop bootstrap columns which contain database records 9 25
.htaccess 5 22
Scope of $_SESSION 17 30
hosting images 4 12
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Creating and Managing Databases with phpMyAdmin in cPanel.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now