Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert MS SQL query to MySQL query

Posted on 2014-09-05
11
Medium Priority
?
747 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 49

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

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 49

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 49

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 35

Accepted Solution

by:
Dan Craciun earned 2000 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 49

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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

722 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