mySQL Query help

[Makavelli]

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?

[Zef Hemel]

MySQL does not support subselects for as far as I know.

[Michele]

Read up on INNER JOIN and/or LEFT JOIN, that will do what you want.

[Spaceman-Spiff]

yup, mysql doesnt support nested queries
you have to nest it in the php part

[Makavelli]

alright.. i'll do that. thnx a lot.. at least now i won't go crazy trying to figure it out..   

[Michele]

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

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

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]

[Spaceman-Spiff]

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