[SOLVED] help with querying 3 tables at once

[dadamssg]

i have a table that stores events, a table that stores the comments for those events, and then another table that stores the attendance of those events. i want to write a cron job script so when the event ends it gets deleted along with its comments and attendance records. i'm not real good with joins and what-nots. heres the query to select expired events

 

$query = "SELECT * FROM test WHERE end < NOW()";

 

all three have a column named 'eventid', so i would need to select the eventid from the events table(test), then select all replies with that eventid and then select all attendance records with that eventid, then delete them...can anyone help?

[dadamssg]

?

[kickstart]

Hi

 

Can just be done directly like this.

 

DELETE FROM attendence WHERE eventid IN (SELECT eventid FROM test WHERE end < NOW() )

DELETE FROM replies WHERE eventid IN (SELECT eventid FROM test WHERE end < NOW() )

DELETE FROM test WHERE end < NOW()

 

However you could probably do it with a constraint on the test table to cascade the delete of to the other tables:-

 

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

 

All the best

 

Keith

[dadamssg]

hey thanks...should that work in phpMyAdmin? i just tried it and i'm getting an error

 

SQL query: Documentation

 

SELECT *

FROM Replies

WHERE eventid

IN (

 

SELECT *

FROM test

WHERE END < NOW( )

)

LIMIT 0 , 30

 

MySQL said: Documentation

#1241 - Operand should contain 1 column(s)

[dadamssg]

you're a genious...it worked! i was stressin about that