9 Joins with GROUP BY and HAVING
GROUP BY and HAVING
can be combined with a join query. For example, suppose you wanted to
find the titles of movies, along with the number of roles in them,
grouped by title and having at least five roles. The titles come from
the Movie table, whereas the number of roles depends on the count of the
rows in the Role table with matching values for movieID. This query
graphically illustrates the idea of a one-to-many relationship. Grouping
is done on the one table, Movie, and counting is done on the many table, Role. HAVING restricts the results to those movies with at least five roles. Example Query 10 illustrates this idea.
Example Query 10
SELECT title, COUNT(roleID)
FROM Movie, Role
WHERE Movie.movieID = Role.movieID
GROUP BY title HAVING COUNT(roleID) >= 5;
The output of Example Query 10 would start as shown in the Example Query 10 Results.
Example Query 10 Results
| title | Expr1001 |
|---|
| Apollo 13 | 6 |
| Batman | 5 |
| Batman & Robin | 11 |
| . . . | . . . |
Example Query 10 is the kind
of query where it can be easy to forget the joining condition because
you are so involved in arranging the grouping and conducting the count;
however, you would immediately recognize that you had made a mistake
when you looked at the results of the query without a joining condition.
10 Three-Way and Multi-Way Joins
In the example database, a
many-to-many relationship exists between the Actor table and the Movie
table. This relationship is captured by embedding the primary key of the
Actor table as a foreign key in the Role table and by embedding the
primary key of the Movie table as a foreign key in the Role table. The
Role table is the table in the middle in this many-to-many relationship.
It is quite possible that someone might want to retrieve information
that comes from related rows in all three of these tables.
Suppose you wanted to find the
actorID and lastName from the Actor table, the roleID and roleName from
the Role table, and the movieID and title from the Movie table. The
critical point is that you would want the actor to match the role
played, and you would want the role to match the movie in which it
appeared. This query would require joining the Actor and Role tables on
the actorID column and simultaneously joining the Role and Movie tables
on the movieID column. Thus, the query must include two joining
conditions, as shown in Example Query 11.
Example Query 11
SELECT Actor.actorID, lastName, roleID, roleName,
Movie.movieID, title
FROM Actor, Role, Movie
WHERE Actor.actorID = Role.actorID
AND Role.movieID = Movie.movieID;
The output of Example Query 11 would start as shown in the Example Query 11 Results.
Example Query 11 Results
| actorID | lastName | roleID | roleName | movieID | title |
|---|
| 00061 | Garland | 00001 | Dorothy Gale | 016 | Wizard of Oz, The |
| 00062 | Morgan | 00002 | The Wizard of Oz | 016 | Wizard of Oz, The |
| 00063 | Bolger | 00003 | The Scarecrow | 016 | Wizard of Oz, The |
| . . . | . . . | . . . | . . . | . . . | . . . |
The example database
contains five tables overall. By definition, because they appear in the
same database, they are all related to one another in some way. The
relationships between the Actor, Role, and Movie tables were reviewed
earlier. In addition, a many-to-many relationship exists between the
Role table and the Quote table. This is captured by embedding the
primary key of the Role table as a foreign key in the RoleQuote table
and by embedding the primary key of the Quote table as a foreign key in
the RoleQuote table. The previous example query—a three-way join—had
three tables and two joining conditions in it. In general, a query with
more than three tables and two joins in it is known as a multi-way join.
An example can be formed by writing a query that depends on the
relationships among all five tables of the example database.
Suppose you wanted to find the
lastName of the actor, the roleName, the title of the movie, and the
quoteText of the quote spoken for all actors, roles, movies, and quotes
in the database. Writing this query involves all five tables, and it
requires four joining conditions, one for each pairing of tables in a
relationship. Example Query 12 shows this query.
Example Query 12
SELECT lastName, roleName, title, quoteText
FROM Actor, Role, Movie, RoleQuote, Quote
WHERE Actor.actorID = Role.actorID
AND Role.movieID = Movie.movieID
AND Role.roleID = RoleQuote.roleID
AND RoleQuote.quoteID = Quote.quoteID;
The output of Example Query 12 would start as shown in the Example Query 12 Results.
Example Query 12 Results
| lastName | roleName | title | quoteText |
|---|
| Garland | Dorothy Gale | Wizard of Oz, The | Toto, I have a feeling we're not in Kansas anymore. |
| Garland | Dorothy Gale | Wizard of Oz, The | Lions and tigers and bears, oh my! |
| Garland | Dorothy Gale | Wizard of Oz, The | There's no place like home. |
| Morgan | The Wizard of Oz | Wizard of Oz, The | Pay no attention to that man behind the curtain! |
| Bolger | The Scarecrow | Wizard of Oz, The | Lions and tigers and bears, oh my! |
| . . . | . . . | . . . | . . . |
Three-way and multi-way
join queries could also be made more complex by the inclusion of the
other features illustrated earlier. Results could be counted, ordered,
grouped, and otherwise manipulated.
