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

 

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.