Re: Delete Related Records
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));
Re: Delete Related Records
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.
Re: Delete Related Records
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:-
Code:
SELECT Table1.Field1
FROM Table1
LEFT JOIN Table2
ON Table1.Field1 = Table2.Field1
AND NOT Table2.PrimaryKeyField IS NULL
LEFT JOIN Table3
etc
Re: Delete Related Records
...Actually scratch that - I think that'll still only return records where all children exist. :blush: 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:-
Code:
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
Re: Delete Related Records
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.
Re: Delete Related Records
Quote:
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.