1

I am getting the following error:

#1064 - 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 'LIMIT 0, 25' at line 1

when using this code:

SELECT * FROM `mytablename`
RIGHT JOIN (
    SELECT wdt_ID, TIMEDIFF (NOW(),mytablename.`Time_stamp`) AS Wait
    FROM `mytablename`
) as t

The error is the same when I use a LEFT JOIN, RIGHT JOIN, or FULL OUTER JOIN but I don't get the error when I use a JOIN only statement. That takes what are only 8 results and repeats them over and over for 200 results that are the same. Where am I going wrong?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
michael
  • 31
  • 6

1 Answers1

1

ADD the join condition like for example:

SELECT * 
FROM mytablename m
RIGHT JOIN (SELECT wdt_ID
                   , TIMEDIFF (NOW(),Time_stamp)
            FROM mytablename ) as t on m.wdt_ID = t.wdt_ID;
VBoka
  • 8,995
  • 3
  • 16
  • 24
  • Thanks so much I totally forgot about that portion of the equation. Why does it sort of work while only using JOIN? – michael Sep 24 '20 at 18:35
  • I have found this: https://stackoverflow.com/questions/16470942/how-to-use-mysql-join-without-on-condition from @GordonLinoff and I believe it will be of help to you... – VBoka Sep 24 '20 at 18:40
  • 1
    Because MySQL [wrongly] interprets a `JOIN` as if it were a `CROSS JOIN`. That strongly deviates from the SQL Standard and I would recommend not to use it. It's confusing for everyone else that is not the author of the code. – The Impaler Sep 24 '20 at 18:48
  • @michael I have to comment that your error in your question is little bi strange ? – VBoka Sep 24 '20 at 18:57
  • @vboka why? The answer was exactly what you said it was and solved the problem. That was the error out of MYPHPADMIN – michael Sep 24 '20 at 19:06
  • Hi @michael because you do not use 'LIMIT 0, 25' at line 1 – VBoka Sep 24 '20 at 19:09
  • 1
    True. Not sure. Just what MYPHPADMIN was giving to me. Problem solved though and the good answers explained why it worked unexpectedly with JOIN – michael Sep 24 '20 at 19:10