Delete Related Records

[robertx]

I have a "Customers" table with auto id field "CustomerID". CustomerID appears as a foreign key in 4 other tables in the same database.

I only want to allow the user to delete a customer that has no related records. I could check each of the 4 tables for the existence of a particular CustomerID but is there a single SQL join statement that I could use to determine whether there are any related records throughout the 4 tables.

[Rahul_PC]

SELECT Table1.Field1
FROM ((Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1) INNER JOIN Table3 ON Table1.Field1 = Table3.Field1) INNER JOIN Table4 ON Table1.Field1 = Table4.Field1
WHERE (((Table1.Field1)=4));

[robertx]

That doesn't give me the result that I am looking for.

Because the joins are all INNER, that query only returns records if there is at least one match for the test foreign key in each of the tables. If there is no match in at least one table then no records are returned.

I need a single query that tells me whether there is at least one match in any table. If there is at least one match then I want to disallow the user from deleting that customer.

[FunkyDexter]

I can suugest two different solutions.

The easiest way is to enforce referential integrity in the database and tell it not to cascade deletes. It will then refuse to delete a record that is a parent of another and throw an error back to your application. Be warned though, this will make your application less portable.

Or you can do roughly what Rahul suggested but use left joins. If you're doing this you will have to check the key field of each of the child tables is not null because it will always return at least one 'null' row:-

SELECT Table1.Field1
FROM Table1 
LEFT JOIN Table2 
   ON Table1.Field1 = Table2.Field1
   AND NOT Table2.PrimaryKeyField IS NULL
LEFT JOIN Table3
etc

[FunkyDexter]

...Actually scratch that - I think that'll still only return records where all children exist. What you actually want to do is Left join all the table first then have the 'Not Is Null's as OR conditions on the end:-

SELECT Table1.Field1
FROM Table1 
LEFT JOIN Table2 
   ON Table1.Field1 = Table2.Field1
LEFT JOIN Table3
   ON Table1.Field1 = Table3.Field1
etc
WHERE (NOT Table2.PrimaryKeyField is null)
OR (NOT Table3.PrimaryKeyField is null)
etc

[robertx]

Nulls aren't a problem bcz I don't use them.

If I use LEFT JOINS, all records will be returned from Table1 even if there are no matches.

If I use INNER JOINS, records will only be returned if there is a match in every table.

[FunkyDexter]

If I use LEFT JOINS, all records will be returned from Table1 even if there are no matches.

Yes, but if there's no match in table B it's values will return as NULL. So by checking that the primary key in table B is NOT NULL you make sure it's a real match you're looking at. By putting the NULL tests in or conditions at the end you ensure you only get a record returned if there's a real match in at least one of the tables.