Topic: Multiple Select

[SharkBait]

I'm trying to get a SELECT statement working so that I can use it for a DELETE.

Though if it's easier I am trying to DELETE rows from 3 tables that reference each other.

Table1.id = Table2.sub_id = Table3.sub_id


id to delete = 42

Code: [Select]

DELETE T1, T2, T3 FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON (T1.id = T2.sub_id) LEFT JOIN Table3 AS T3 ON (T1.id = T3.sub_id) WHERE T1.id = 42

The thing is, there can be rows matching Table1 in Table2 or not. Same goes with Table1 and Table3.

Though I am thinking it has to do with my 2 LEFT JOINs.

Example
T1.id = 42
T2.sub_id = NULL
T3.sub_id = 42

or

T1.id = 42
T2.sub_id = 42
T3.sub_id = NULL

or

T1.id = 42
T2.sub_id = 42
T3.sub_id = 42

So 2 tables link back to a main table. Hopefully this makes some sense. I'd rather not do separate delete statements [/code]

[fenway]

And what's the problem?

[SharkBait]

My SELECT or DELETE returns 0 results.

I have a main table that has 2 related tables. The 2 related tables might or might not have values associated to a row in the main table.

If both tables have rows that match the main table, then I think it works. But if 1 of the sub tables only has a value associated to it then it returns 0.

I want to be able to delete the main table row where ID matches 42
and then any row in either table which might make reference to the main table with a sub ID of 42.

[fenway]

Wait, the select doesn't work either? Post that query first... left join should allow this.