Find Some Helpful Tables in Font

Printer PDF 417 in Font Find Some Helpful Tables

Let s look at the key words in the query Which teams have a coach as a manager We have the nouns team, coach, and manager. We have a table called Team. Coach and Manager are fields in the Member and Team tables, respectively. So the Team and Member tables look like a good place to start.

Next, take a look at the data in the tables and see how you would decide if a team had a coach as a manager. Figure 10-11 shows the relevant columns of the two tables.

We can find the IDs of the two team managers easily enough. They are the values in the Manager column of the Team table (239 and 153). Now, how do we check if these members are coaches Looking at the Member table, we see that the coaches are in the Coach column. We need to check if either of our two managers appears in the Coach column. Member 239 doesn t appear, so his team (TeamA) is not managed by a coach. Member 153 does appear somewhere in the Coach column, so his team (TeamB) is managed by a coach. So we have answered our query. TeamB is managed by a coach.

Our query is Which teams have a coach as a manager So following what we did in the previous section, we can write a description of what the rows we retrieve should be like. This is where I like to have imaginary fingers pointing to the relevant rows to make it easier to describe the query, as in Figure 10-12. We are going to check every team to decide if it should be retrieved. The condition that would allow us to decide is something like this (we ll look at a different way shortly): I ll write out the TeamName from row t, where t comes from the Team table, if there exists a row (m) in the Member table where the value of coach (m.Coach) is the manager of the team (t.Manager).

In a slightly more formal way (if you prefer), we can write this as the relational calculus expression in Listing 10-14.

Listing 10-14. Relational Calculus Expression for Finding Teams with a Coach As a Manager {t.TeamName | Team(t) and (m) Member(m) and t.Manager = m.Coach}

We can now translate this almost directly into SQL using a nested query (discussed in 4). One possibility is shown in Listing 10-15.

Listing 10-15. Finding Teams with Coaches As Managers (One Way) SELECT t.TeamName FROM Team t WHERE EXISTS (SELECT * FROM Member m WHERE m.Coach = t.Manager)

First attempts at queries aren t necessarily the most elegant. After all, we are following this route because we were stumped in the first place. Following the technique of solving the query by hand and describing the conditions helps you understand what you are trying to do. That often makes the query seem much easier than you first thought.

Having done the query as described in the previous section, I then realized that I could have thought of it this way: the manager just has to be in the set of coaches. I can easily find the list of coaches with a simple query, and then use that in a nested query, as shown in Listing 10-16.

Listing 10-16. Finding Teams with Coaches As Managers (Another Way) SELECT t.TeamName FROM Team t WHERE t.Manager IN (SELECT m.Coach FROM Member m)

For me, the query in Listing 10-16 is simpler and easier to understand than the one in Listing 10-15, so I would probably prefer to use that one. As always, there are often many ways to achieve the same result. We could have done an inner join on the Team and Member tables with the join condition being t.Coach = m.Manager. The managers who don t appear in the Coach column will not appear in the inner join (see the section on outer joins in 3). The SQL for this approach is shown in Listing 10-17.

Listing 10-17. Finding Teams with Coaches As Managers Using an Inner Join SELECT DISTINCT t.TeamName FROM Team t INNER JOIN Member m ON t.Manager = m.Coach

Personally, I don t find the query in Listing 10-17 particularly intuitive. I doubt if someone else looking at the query would easily understand its purpose. I still like Listing 10-16 best in terms of ease of understanding. You might also like to check the efficiency of each of the queries (discussed in 9), if you think that might be important (unlikely in this case). For SQL Server 2005, each of the queries in Listings 10-15, 10-16, and 10-17 had the same execution plan, so they were all carried out in exactly the same way under the hood.