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?
MySQL does not support subselects for as far as I know.
Read up on INNER JOIN and/or LEFT JOIN, that will do what you want.
yup, mysql doesnt support nested queries
you have to nest it in the php part
alright.. i'll do that. thnx a lot.. at least now i won't go crazy trying to figure it out.. ;D
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?
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
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.
I thought the latest version of MySQL added sub selects?
Quote from: Gobalopper on August 08, 2003, 07:37:04 PM
I thought the latest version of MySQL added sub selects?
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]
ah yes, it's in mysql 4.1.x
didnt know that...
but not everyone uses that yet...