SELECT * FROM mytable INNER JOIN etc. ok, but SELECT * FROM mytable WHERE... INNER JOIN error

Question

I am trying to do an INNER JOIN on two queries. If I have a broad SELECT statement for the first component, it works fine. If I try to add a WHERE... to the first component, I get an uninformative Syntax error and I don't know why.

This is what works but is slow because my table is big (~1mil rows)

SELECT a.RoomNum, a.EventName, a.EventStatus, a.EventDateTime FROM eventtable a
INNER JOIN
(
    SELECT `RoomNum`, eventname, MAX(`EventDateTime`) as MT
    FROM `eventtable`
    WHERE eventname = "DND"
    Group BY RoomNum
) b
ON a.RoomNum = b.RoomNum and a.EventDateTime = b.MT and a.eventname = b.eventname

But what I really need is:

SELECT a.RoomNum, a.EventName, a.EventStatus, a.EventDateTime from eventtable WHERE a.EventName = "DND" a
INNER JOIN
(
    SELECT `RoomNum`, eventname, MAX(`EventDateTime`) as MT
    FROM `eventtable`
    where eventname = "DND"
    Group by RoomNum
) b
on a.RoomNum = b.RoomNum and a.EventDateTime = b.MT and a.eventname = b.eventname

I thought of assigning result to a table but didn't really want to have to generate a table and then drop it after to make sure next time the query is made, there will be no table already present.

Thanks.

@scrowler The uninformative syntax error is: "#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 'name = "DND" INNER JOIN ( SELECT `RoomNum`, eventname, MAX(`EventDateTime`) as M' at line 1" — janson, May 04 '15 at 05:11

score 1 · Answer 1 · answered May 04 '15 at 04:33

1

Firstly, JOIN can be on two tables. And while you write that you should not add conditions on those two. If you wish to, add them at the end.

Put the where clause towards the end and it will work.

answered May 04 '15 at 04:33

bhanuprathap

71
3

score 0 · Accepted Answer · answered May 04 '15 at 04:23

0

Your query should be

SELECT a.RoomNum, a.EventName, a.EventStatus, a.EventDateTime from eventtable a
INNER JOIN
(
    SELECT `RoomNum`, eventname, MAX(`EventDateTime`) as MT
    FROM `eventtable`
    where eventname = "DND"
    Group by RoomNum
) b
on a.RoomNum = b.RoomNum and a.EventDateTime = b.MT and a.eventname = b.eventname
WHERE a.EventName = "DND"

Observe where is at the end.

answered May 04 '15 at 04:23

Abhishek

6,912
14
59
85

Your solution as well as Dhaval works. My thinking was that the end result of the first SELECT gets JOINed with the second one. This is why I wanted to limit my first table so that it would be a JOIN of two smaller tables. The placement of the WHERE suggests this is not the case and the time required supports this. My First select done alone takes 0.0156s, my inner select takes 1.34s, but doing the whole filter takes 40s which is too long...Is there any way to reduce the table sizes before the INNER JOIN is executed? – janson May 04 '15 at 05:36
I guess, yes. You can give alias to the table after `where` clause and then join them – Abhishek May 04 '15 at 05:39
I tried before I asked my question with assigning an alias, but just got a "have an error in your SQL syntax". I've tried SELECT... FROM... WHERE... AS myAlias INNER JOIN. Since learning from you and others that the WHERE needs to be at the absolute end of the query, I tried SELECT... FROM... eventtable INNER JOIN... ON... WHERE a.EventName = "DND" AS myAlias. Also errors out. Sorry, my SQL knowledge is really limited. – janson May 04 '15 at 05:58
ok.. let me try to find the syntax. Till then read http://stackoverflow.com/questions/16632901/how-to-do-where-clause-before-inner-join , it should help you. BTW, inbuilt query optimizer should optimize your query. – Abhishek May 04 '15 at 06:03
Thanks for pointing me to that post. Never knew about query optimizer till you mentioned it. I'm trying to understand [link](https://www.simple-talk.com/sql/sql-training/the-sql-server-query-optimizer/) but based on what I read it seems like MySQL optimizer should have considered A(filtered) JOIN B vs A JOIN B(filtered) and decided what was best. The 40s is already the optimal search which ever that one may be. Is that correct? – janson May 04 '15 at 07:06
Yes, query optimizer does it for you. You might be able to reduce the query time, if you index columns. Though indexing will increase your db size ;) – Abhishek May 04 '15 at 07:08

score 0 · Answer 3 · answered May 04 '15 at 04:32

Where Clause comes at END ... and you place it Between from and INNER join so it cause Syntax ERROR...so you can do something Like this

    SELECT a.RoomNum, a.EventName, a.EventStatus, a.EventDateTime from eventtable a
    INNER JOIN
    (
        SELECT `RoomNum`, eventname, MAX(`EventDateTime`) as MT
        FROM `eventtable`
        where eventname = "DND"
        Group by RoomNum
    ) b
    on a.RoomNum = b.RoomNum and a.EventDateTime = b.MT and a.eventname = b.eventname
 WHERE a.EventName = "DND"

or If you do not want to use where clause than you can directly check condition in join like this..

SELECT a.RoomNum, a.EventName, a.EventStatus, a.EventDateTime from eventtable a
INNER JOIN
(
    SELECT `RoomNum`, eventname, MAX(`EventDateTime`) as MT
    FROM `eventtable`
    where eventname = "DND"
    Group by RoomNum
) b
on a.RoomNum = b.RoomNum and a.EventDateTime = b.MT and a.eventname = b.eventname and  a.EventName = "DND"

SELECT * FROM mytable INNER JOIN etc. ok, but SELECT * FROM mytable WHERE... INNER JOIN error

3 Answers3