Solved

Convert MS SQL query to MySQL query

Posted on 2014-09-05
11
744 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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 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 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

636 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