<<

. 18
( 132 .)



>>

Figure 3-8: Results of query using functions and aliases


You can also use aliases on tables. This will be particularly helpful when you™re
dealing with multiple tables. You can read about this in further detail in the section
“The multi-table join.”

sum()
The sum() function returns the sum of a given column and is almost always used
with a group by clause. For instance, if you are running an application for a non-
profit, you might want to know the total contributions from each state. The table
you™re working with might look like the one in Figure 3-9.
78 Part I: Working with MySQL




Figure 3-9: Table where using sum() would be helpful


To get the total from each state you™d run the following query:

select state, sum(contribution) from contributions group by state;

min()
The min() function pulls out the lowest value in each grouping. To find the lowest
contribution from any state just make a small change to the previous query:

select state, min(contribution) from contributions group by state;

max()
As you probably guessed, max() will return the highest value in a group:

select state, max(contribution) from contributions group by state;

avg()
avg () returns the average of the group:

select state, sum(contribution) from contributions group by state;

You can throw all these together to create a pretty useful query, as Figure 3-10
and the following query show:
Chapter 3: Inserting, Editing, and Selecting Data 79

select state, sum(contribution) as ˜Total™,
avg(contribution) as ˜Average™,
min(contribution) as ˜Minimum™,
max(contribution) as ˜Maximum™
from contributions
group by state;




Figure 3-10: Using multiple aggregate functions together


group by OPTIONS
Most relational databases require that fields listed in the select clause be used in
the group by predicate. But MySQL gives you a second option: You can group a
subset of the columns listed. For instance, if you want to find out the number of
people in one city and get a look at a sample ZIP code from that city, you could run
the following:

select city, zip, count(*) from users group by city;

The query would return a listing of cities, the number of entries for each city,
and one sample ZIP code. These results are quite different from those from the fol-
lowing query:

select city, zip, count(*) from users group by city, zip;
80 Part I: Working with MySQL

This returns a separate row for each city/ZIP combination and provides a count
for each unique combination.

having
The having predicate restricts the rows displayed by a group by. This predicate is
not the same as the where clause. The where clause actually restricts the rows that
are used in the group by, whereas the having clause only prevents their display.
If you need to find the average amount of donations from each state for all those
who contributed more than $100, you could run the following:

select avg(donations), state from contributions where donations> 100;

However, if you want to display average contributions for all the states in which
the average was over $100, you have to use the having clause. Because the having
clause does not restrict rows that go into the group by, the aggregate functions,
like avg()in this case, use all the rows in their calculations.

select avg(contribution) as avg_contrib, state
from contributions
group by state
having avg(contribution)>100;




Joining Tables
If you read Chapter 1, you know that relational databases work so well because
they segment information. Different tables hold information on different topics,
and fields are inserted into the tables to maintain relationships. After you finish the
normalization process, it™s likely that none of your tables will be usable without the
others. That is why you™ll need to join tables in your SQL select statements.

The two-table join (equi-join)
For the sake of continuity, we™re going to reprise a couple of tables first seen in
Chapter 1. Take a look at the familiar tables in Figure 3-11.
If you™re looking to do a mailing to all the people in the contacts table, you are
going to need to join the contacts table to the companies table, because the street
address is in the companies table (and that™s exactly where it should be). The
company_id column in the contacts table creates the relationship between these
tables. And if you join these tables on occasions in which the company_id field in
the contacts table is equal to the company_id field in the contacts table, all the
information will be at your fingertips.
Chapter 3: Inserting, Editing, and Selecting Data 81


companies
company_id company_name address
1 Big Co Company 1121 43rd St
2 Little Co Company 4444 44th St

contacts
contact_id company_id Name Title Phone Email
1 1 Jay Greenspan Vice President 4155551212 1121 43rd St
2 1 Brad Bulber President 4155552222 4444 44th St
3 2 John Doe Lacky 2125556666 4444 44th St
Figure 3-11: Tables in need of a join


Making this join is easy enough in SQL. All the tables to be joined must be listed
in the from portion of the select statement. And the fields on which the join takes
place must be listed in the where portion:

select *
from companies, contacts
where companies.company_id = contacts.company_id;

At those times when a reference to a field name is ambiguous, you need to spec-
ify which table the column comes from by using the syntax table_name.
column_name. You can do this in the where clause in Figure 3-12. If you fail to
indicate the table from which you™re pulling the column in the SQL statements,
MySQL will return an error.
This type of join, in which tables are merged based on quality in a common field,
is extremely common. It is known as an equi-join or inner join. The name “inner
join” will make more sense when you learn about the outer join later in this chapter.
Once you begin performing joins, aliases become convenient. By specifying an
alias in the from clause you can save yourself some typing. In the following code,
t1 is an alias for companies and t2 is an alias for contacts.

select *
from companies t1, contacts t2
where t1.company_ID = t2.company_ID;


The multi-table join
An equi-join can be applied to more than one table. Many of your SQL statements
will join three, four, or more tables. All you™ll need to do is add additional columns
after select, additional tables in the from clause, and the additional join parameters
in the where clause. Take a look at the tables that need multiple joins in Figure 3-13.
82 Part I: Working with MySQL




Figure 3-12: A basic join


If you want to find the addresses for all the companies with offices in California
and that have expertise in consulting, you have to join all four of these tables. The
following query gets the job done. Here the where clause contains quite a few tests:
The first two lines of the where clause limit the rows that will be returned to those
companies that match your criteria. The remainder of the where clause takes care of
the joins.

select *
from companies, locations, expertise, companies_expertise
where state = ˜CA™ and
companies_expertise.expertise_ID = 3 and
companies.company_ID = companies_expertise.company_ID and
companies.company_ID = locations.company_ID and
companies_expertise.expertise_ID = expertise.expertise_ID
;


The outer join
The challenges presented by null values have shown themselves repeatedly in this
book. In Chapter 2, we presented Tables 2-1 and 2-2, which we™re re-presenting
here as Tables 3-13 and 3-14.
Chapter 3: Inserting, Editing, and Selecting Data 83


company_id name expertise_id area
1 IBM 1 Hardware
2 Xerox 2 Software
3 Sun 3 Consulting

company_id expertise_id
1 1
1 2
1 3
2 1
2 3
3 1
3 2


location_id company_id address state
1 1 4 My Way, Durham NC
2 2 44 Circle Dr, New York NY
3 1 1 Front St, San Francisco CA
4 2 Park Dr, Palo Alto CA
5 2 48 Times Square, New York NY
6 3 280 South, Sunnyvale CA
Figure 3-13: Tables in need of multiple joins



TABLE 3-13 THE CONTACTS TABLE

first_name last_name fantasy_spouse_id

Jay Greenspan 1

Brad Bulger NULL




TABLE 3-14 desired_spouse

fantasy_spouse_id first_name last_name

1 Nicole Kidman
84 Part I: Working with MySQL

Now imagine that you need to get a list of the contacts and their desired spouses.
The equi-join shown in the previous section will not work in this case. Take the fol-
lowing query:

select *
from contacts, desired_spouse
where contacts.fantasy_spouse_id = desired_spouse.fantasy_spouse_id;

Only the first row of the contacts table will be returned. The null value in the
second row ensures that nothing can match the criterion in the where clause. In
cases like this, wherein you need to preserve one table and join the second table
when there are matching values, you can make use of the outer join (also known as
the left outer join), which looks like this:

select *
from contacts
left join desired_spouse
on contacts.fantasy_spouse_id = desired_spouse.fantasy_spouse_id;

This statement says, “I want to keep the entire contacts table, and tack on the
spouses table when these two fields are equal.” The word left in the term left outer
join refers to the fact that when you visualize your database tables, you should
visualize the first table, the one that appears in the from clause, on the left side, and
the joined table on the right.


Depending on the database package you™re using, the syntax of the outer
join may vary. Some databases support left, right, and full (both left and
right) outer joins. MySQL only has the left outer join, but in practice that™s
usually all you need. You can either use the syntax in the previous query or
use left outer join on.


<<

. 18
( 132 .)



>>