[Solved] MySQL Multiple Left Outer Join Query Question involving 3 tables
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

MySQL Multiple Left Outer Join Query Question involving 3 tables

Database type:

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?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  2. Explicitly ORDER BY After GROUP BY (modified query below): By default, the database sorts all 'GROUP BY col1, col2, ...' queries as if you specified 'ORDER BY col1, col2, ...' in the query as well. If a query includes a GROUP BY clause but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying 'ORDER BY NULL'.
Optimal indexes for this query:
ALTER TABLE `Bite` ADD INDEX `bite_idx_id` (`id`);
ALTER TABLE `Dog` ADD INDEX `dog_idx_id_name` (`id`,`Name`);
ALTER TABLE `Flea` ADD INDEX `flea_idx_dog_id_bite_id` (`Dog_id`,`Bite_id`);
The optimized query:
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 
    ORDER BY
        NULL

Related Articles



* original question posted on StackOverflow here.