I'm building a simple photo gallery type application, and have the current table structure:

topcat (this is the top level image category)
>subcat1 (subcategory1 - referenced to topcat by foreign id)
>subcat2 (subcategory2 - referenced to subcat1 by foreign id)
>imageattributes (contains actual images - links back via foreign id to EITHER subcat1 or subcat2)

here is a link to the sql file that will replicate the exact DB i'm working with:
http://www.webfixes.com/bettiedb.zip

Now on to my 2 questions - I'm working on a tool that will allow her to delete a top category and EVERYTHING under it, or a subcat1 category and everything under that...and so on down to individual image level

Right now i'm doing a SELECT rather than a delete, for testing purposes, and so far I have this:

select topcat.*,subcat1.*,subcat2.*,imageattributes.* from topcat,subcat1,subcat2,imageattributes where topcat.id = 1
AND
topcat.id = subcat1.topcat_id
AND
subcat1.id = subcat2.subcat1_id
AND (
(imageattributes.subcat1entry = 1 AND imageattributes.subcat1or2_id = subcat1.id)
OR
(imageattributes.subcat2entry = 1 AND imageattributes.subcat1or2_id = subcat2.id)
)

When run against the exact database i currently have set up in mysql control center, this seems to do pretty much what i want - however if you run this query:

select topcat.*,subcat1.*,subcat2.* from topcat,subcat1,subcat2 where topcat.id = 1
AND
topcat.id = subcat1.topcat_id
AND
subcat1.id = subcat2.subcat1_id

Column #8, Row #2 has a name value of Tindouf(a city in algeria) - this record wasn't included in the first query

I'm pretty sure i know WHY it wasn't included (the last AND condition from the first sql statement exludes it b/c there are no actual picture files for that category), but if someone deletes a top level category and everything under it, i want the "Tindouf" row to be deleted as well - In other words, if you delete the top level category Africa, ALL things that fall udner Africa should be deleted, whether they contain image data or not....what do i need to change in order to accomplish this?

Question #2: As a side note, I'd also like to be able to somehow collect all the image names targeted in the delete sql statement...these are image paths to actual files on the web server, so it would be nice if i could build a quick array of all the image names fixing to be deleted AS i'm in the process of deleting them - so i could go back and delete them off the server afterwards - any way to incorporate that into one sql statement?


sorry for such a long post - any help is greately appreciated

just wanted to give an update - looks like the problem is fixed - a moderator from another forum gave this answer which works:
I think what you need is a LEFT JOIN:
http://dev.mysql.com/doc/mysql/en/JOIN.html

Something like this (untested):
PHP:
--------------------------------------------------------------------------------

select topcat.*, subcat1.*,subcat2.*,imageattributes.*
from topcat
LEFT JOIN subcat1
ON topcat.id = subcat1.topcat_id
LEFT JOIN subcat2
ON subcat1.id = subcat2.subcat1_id
LEFT JOIN imageattributes
ON (imageattributes.subcat1entry = 1 AND imageattributes.subcat1or2_id = subcat1.id)
OR
(imageattributes.subcat2entry = 1 AND imageattributes.subcat1or2_id = subcat2.id)
where topcat.id = 1