Delete Statement

[riches85]

I am developing a statistical database for a volleyball team that I play on and I am having some trouble writing a delete command. My problem comes when I want to delete an entire season (Year). When i do this, I also want it to delete any games, stats, and rosters for that year. I can join the tables like follows:

DELETE FROM Seasons, Rosters, Games, Game_Stats
USING Seasons s, Rosters r , Games g, Game_Stats gs
WHERE s.Season_ID = 2001
AND s.Season_ID = r.Season_ID AND
r.Season_ID = YEAR(g.Date)
AND g.Game_ID = gs.Game_ID

I have tried every possible combination to delete everything at once, but failed everytime. I have used many combinations of OR statements, And statements, etc and nothing works. Sometimes it will delete the records if there or records referencing it, but i run into the biggest problem when i just create one and it has nothing linked to it. I guess im just looking for some way to delete everything. If anyone has any ideas or suggestions, it would be greatly appreciated. thanks

 

[KarveR]

I prefer to mark stuff redundant, therby I'm able to hide it and then use it again should the need arise.

5 years time, what if you want to do an average score per season or something like that?

think ahead ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.