General Community > Scripting Help

mySQL Query help

<< < (2/2)

Michele:

--- Quote from: Makavelli on August 08, 2003, 10:52:19 AM ---I'm trying to use this select statement but it doesn't seem to work.

------------------
SELECT *
FROM notifications
WHERE notifications.eventID =(SELECT events.eventID FROM events WHERE eventDate < CURRENT_DATE)
------------------

but i'm getting this error:

Error Code : 1064
You have an error in your SQL syntax near 'SELECT events.eventID FROM events WHERE eventDate < CURRENT_DATE)' at line 3

can someone please help?

--- End quote ---

SELECT not.*
FROM notifications as not
LEFT JOIN events
ON events.eventID =not.eventID
WHERE events.eventDate < CURRENT_DATE

Try that and let me know if it works!
Michele

Iridium:
You may find it faster to use a JOIN rather than a LEFT JOIN as (from what I've seen) JOIN makes better use of any indices/keys that may be defined.

Gobalopper:
I thought the latest version of MySQL added sub selects?

[Unknown]:

--- Quote from: Gobalopper on August 08, 2003, 07:37:04 PM ---I thought the latest version of MySQL added sub selects?

--- End quote ---

It did, but not like the one used above...

This is the faster way to do it (right join, instead of left join.)

SELECT n.*
FROM notifications as n, events AS e
WHERE e.eventDate < CURRENT_DATE()
   AND e.eventID = n.eventID

-[Unknown]

Spaceman-Spiff:
ah yes, it's in mysql 4.1.x
didnt know that...
but not everyone uses that yet...

Navigation

[0] Message Index

[*] Previous page