I noticed a few mistakes in my query, which may be causing some
confusion and would probably cause it not to work. But I'll break
everything down.
The NULLs in the second owner column are the indicators that there is
no matching owner in the most recent three months.
Breaking down the query,
I have this table of events. Each event has an owner
id and the time that it happened.
What I want to do is delete all events
more than three months old but only if the owner does not own
any newer events.
The coolest would just be a single DELETE query.
Can this be done?
Mysql 4.0.18
--
MySQL
If you post the table structure (SHOW CREATE TABLE tablename\G) we could
help you write this statement.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Gerald Taylor [EMAIL PROTECTED] wrote on 11/10/2004 11:52:35 AM:
I have this table of events. Each event has an owner
id
Try something like this:
SELECT Events.ID, Events.ownerID, Owners.ownerID
FROM Events
LEFT JOIN Events AS Owners
ON Events.ownerID=Owners.ownerID AND Events.eventData 3 months ago
WHERE Owners.ownerID IS NULL
I know you want to do a delete, but play with SELECT first to make sure
it's doing what
The parts I am interested in:
(I won't bore you with the fields not relevant to this problem )
CREATE TABLE events (
e_id int(15) NOT NULL auto_increment,
e_owner int(15) NOT NULL default '0',
e_time int(15) NOT NULL default '0',
other junk omitted
PRIMARY KEY (e_id)
)
