CODEWITHSUNDEEP
mysqlsqlleft-join
user2643679
user2643679

Reputation: 705

SQL DELETE LEFT JOIN query

My query is:

DELETE p,p2,r 
FROM Profiles p
LEFT JOIN Pics p2 ON p.ID=p2.ProfileID
LEFT JOIN Food f ON p.ID=f.ProfileID
WHERE p.ID = 46

This query deletes the User's Profile, all pictures in the Pics table and all matching ProfileID in food table. Which would leave me ID 47,48 in Profile table ID 66,67 in Pics table ID 30,31,32,34 in Food table

But I want it to also delete all the rows where the Pics.ID ProfileID is 46, so Pics.ID 64 and 65. These two IDs to be deleted from the Food table. which would be Food.ID 30 and 32 so left with just: ID 31, 34 in Food table

Profile Table:

ID Gender
46 male
47 female
48 female

Pics Table:

ID ProfileID Position
64 46        1 
65 46        2
66 47        1
67 48        1

Food Table:

ID PicID ProfileID
30 64    47
31 66    48
32 65    47
33 67    46
34 67    47

Upvotes: 2

Views: 6138

Answers (1)

Chris Lear
Chris Lear

Reputation: 6732

I think this is what you want

DELETE p,p2,f,f2 
FROM Profiles p
LEFT JOIN Pics p2 ON p.ID=p2.ProfileID
LEFT JOIN Food f ON p.ID=f.ProfileID
LEFT JOIN Food f2 ON p2.ID=f2.PicID
WHERE p.ID = 46

Upvotes: 7

Related Questions