MySQL multiple left external query to enter a query with 3 tables

Question

Due to 0 answers, I assume that my LEFT JOIN question received too much database information that was too esoteric. I have already programmed the problem, but I still would like to know how to join this scenario:

Suppose that the basic strategy is a surrogate key (each table has an id field that only automatically increments), as well as a foreign key for its obvious parent. Words in all caps can be considered as tables.

Say you have a database containing DOGS. Example: Wolf, Winston, Butch and Benny

Each DOG has a FLEA. (for simplicity, allows one flea to live on only one dog and leave this ratio from 1 to many). Fleas have id as names or something else, along with what color they have.

Each FLEA will BITE its DOG host several times, and it is stored in this database and is recorded daily.
Field identifier (PK), flea identifier (FK), date, bit_time.

Say you want to get the total number of times each dog has been bitten (it's 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 had to add criteria to the WHERE clause, restricting it to Brown fleas, and Brown flea never beat 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 has no result

How would you rewrite the query so that the name Benny is still displayed with 0 (preferred) or NULL in total, and not just with Benny completely excluded from the result?

This looks like a few left outer joins .. but with a few tables like this, the documentation that is easily detected does not seem to answer a question involving 3 tables with a filter of values ​​in the middle of three tables.

Does anyone have any tips on how to rewrite this to allow multiple left outer joins, or use another method to store all the dog 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 

Using LEFT JOIN s, you pull all Dog entries, even those that don't have matching fleas (for which the columns from the join will be NULL). You can use COALESCE to set times_bitten to 0 if no entries are found (otherwise it will be NULL).

You probably also want to group Dog.id instead of Dog.Name (if it is not possible for him to have multiple dogs with the same name?)