CODEWITHSUNDEEP
mysqlsqlleft-joininner-join
StealthRT
StealthRT

Reputation: 10552

mySQL delete with innerjoin with 3 needed tables

Hey all i am trying to use my query below to delete ALL records dealing with the account number

SELECT * FROM userAccount UA 
   INNER JOIN userCase UC ON UA.AccNum = UC.Userlink 
WHERE UA.AccNum = 9571612;

This does produce 2 rows as it should (there are 2 cases associated with the account #).

However, doing this (what i need to do):

SELECT * FROM userAccount UA 
    INNER JOIN userCase UC ON UA.AccNum = UC.Userlink 
    INNER JOIN authPerson AP ON AP.Usercase = UC.CaseNum 
WHERE UA.AccNum = 9571612;

This works fine IF there are cases BUT doesnt if there are none to find in the INNER JOIN authPerson AP ON AP.Usercase = UC.CaseNum

If i change both INNER JOIN's to LEFT JOIN's i do get data.. Just want to make sure this is the correct way of doing this.

Upvotes: 0

Views: 80

Answers (2)

Jenifer_justin
Jenifer_justin

Reputation: 167

You can try with either INNER JOIN or LEFT JOIN , Since you want to delete all records dealing with the account number, better use the LEFT JOIN as it deletes even if the records do not match:

DELETE * FROM userAccount UA 
LEFT JOIN userCase UC ON UA.AccNum = UC.Userlink 
LEFT JOIN authPerson AP ON AP.Usercase = UC.CaseNum 
WHERE UA.AccNum = 9571612;

Upvotes: 1

sgeddes
sgeddes

Reputation: 62861

This is the correct behavior. Using an INNER JOIN all records must exist in all joined tables. Using an OUTER JOIN, the record only has to exist in the main table (in this case, your useraccount table).

In regards to your question, if you want to delete from the userAccount table if the record also exists in the other tables, then use an INNER JOIN. If however it doesn't matter, you would need an OUTER JOIN.

EDIT -- based on your comment, for matching records, this will delete from all the tables:

DELETE UA, UC, AP
FROM userAccount UA
    INNER JOIN userCase UC ON UA.AccNum = UC.Userlink 
    INNER JOIN authPerson AP ON AP.Usercase = UC.CaseNum 
WHERE UA.AccNum = 9571612;

Or you can use an OUTER JOIN to delete even if the records do not match:

DELETE UA, UC, AP
FROM userAccount UA
    LEFT JOIN userCase UC ON UA.AccNum = UC.Userlink 
    LEFT JOIN authPerson AP ON AP.Usercase = UC.CaseNum 
WHERE UA.AccNum = 9571612;

Upvotes: 0

Related Questions