MySQL Multiple Left Outer Join Query Question involving 3 tables

Question

Due to 0 responses, I'm guessing that my LEFT JOIN question got into too much detail about a database that was too esoteric. I've already programmed around the issue, but I'd still like to know how to join in a similar scenario:

Assume a basic surrogate key strategy (each table has an id field that just auto-increments), as well as a foreign key to its obvious parent. Words in all caps can be considered tables.

Say you have a Database containing DOGS. Example: Wolfie, Winston, Butch, and Benny

Each DOG has FLEAs. (for simplicity lets make it so that one flea lives on only one dog and leave this a 1 to many relationship). The fleas have id's as names or whatever, along with what color they are.

Each FLEA will BITE it's DOG host several times, and that is stored in this database, and recorded daily.
Fields id(PK), flea id(FK), date, times_bitten.

Say you want to get the total number of times each dog was bitten (this is easy)

SELECT Dog.Name, sum(Bite.times_bitten)
FROM Dog, Flea, Bite
WHERE Dog.id = Flea.Dog_id and Bite.id = Flea.Bite_id
GROUP BY Dog.Name

Let's say that you were to add criteria to the "WHERE" clause limiting it to "Brown" fleas, and no "Brown" Fleas ever bit Benny.

SELECT Dog.Name, sum(Bite.times_bitten)
FROM Dog, Flea, Bite
WHERE Dog.id = Flea.Dog_id and Bite.id = Flea.Bite_id and Flea.color = "Brown"
GROUP BY Dog.Name

Benny is not in the result

How would you rewrite the query so that Benny's name would still show up, with either a 0(preferably) or NULL in the sum filed, rather than just having Benny eliminated altogether from the result?

This seems like its about multiple left outer joins.. but with multiple tables like this, the documentation that's readily findable doesn't seem to answer a question involving 3 tables with a value filter in the middle of the 3 tables.

Does anyone have advice on how to rewrite this to allow for multiple left outer joins, or have some other method of keeping all of the dog's names in the query even if the sum = 0?

Answer 1

This should work:

SELECT Dog.Name, COALESCE(SUM(Bite.times_bitten), 0) AS times_bitten
FROM Dog
LEFT JOIN Flea
  ON Flea.Dog_id = Dog.id
    AND Flea.color = "Brown"
LEFT JOIN Bite
  ON Bite.id = Flea.Bite_id
GROUP BY Dog.Name

By using LEFT JOINs, you will pull all the Dog records, even those without corresponding Fleas (for which the columns from the join will be NULL). You can use COALESCE to set times_bitten to 0 if no records are found (otherwise it would be NULL).

You probably also want to group by Dog.id instead of Dog.Name (unless it is impossible for there to be multiple dogs with the same name?)