Oracle 5.0 Reference Manual Download

Page: 233 / 3232

Getting Information About Databases and Tables

213

Based on what you have learned from the queries that you have run on the

pet

table, you should be

able to perform retrievals on the records in the

event

table; the principles are the same. But when is

the

event

table by itself insufficient to answer questions you might ask?

Suppose that you want to find out the ages at which each pet had its litters. We saw earlier how to
calculate ages from two dates. The litter date of the mother is in the

event

table, but to calculate

her age on that date you need her birth date, which is stored in the

pet

table. This means the query

requires both tables:

mysql>

SELECT pet.name,

(YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,

remark

FROM pet INNER JOIN event

ON pet.name = event.name

WHERE event.type = 'litter';

+--------+------+-----------------------------+

| name | age | remark |

+--------+------+-----------------------------+

| Fluffy | 2 | 4 kittens, 3 female, 1 male |

| Buffy | 4 | 5 puppies, 2 female, 3 male |

| Buffy | 5 | 3 puppies, 3 female |

+--------+------+-----------------------------+

There are several things to note about this query:

• The

FROM

clause joins two tables because the query needs to pull information from both of them.

• When combining (joining) information from multiple tables, you need to specify how records in one

table can be matched to records in the other. This is easy because they both have a

name

column.

The query uses

clause to match up records in the two tables based on the

name

values.

The query uses an

INNER JOIN

to combine the tables. An

INNER JOIN

permits rows from either

table to appear in the result if and only if both tables meet the conditions specified in the

clause.

In this example, the

clause specifies that the

name

column in the

pet

table must match the

name

column in the

event

table. If a name appears in one table but not the other, the row will not appear

in the result because the condition in the

clause fails.

• Because the

name

column occurs in both tables, you must be specific about which table you mean

when referring to the column. This is done by prepending the table name to the column name.

You need not have two different tables to perform a join. Sometimes it is useful to join a table to itself,
if you want to compare records in a table to other records in that same table. For example, to find
breeding pairs among your pets, you can join the

pet

table with itself to produce candidate pairs of

males and females of like species:

mysql>

SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species

FROM pet AS p1 INNER JOIN pet AS p2

ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';

+--------+------+--------+------+---------+

+--------+------+--------+------+---------+

| Fluffy | f | Claws | m | cat |

| Buffy | f | Fang | m | dog |

| Buffy | f | Bowser | m | dog |

+--------+------+--------+------+---------+

In this query, we specify aliases for the table name to refer to the columns and keep straight which
instance of the table each column reference is associated with.

3.4. Getting Information About Databases and Tables

What if you forget the name of a database or table, or what the structure of a given table is (for
example, what its columns are called)? MySQL addresses this problem through several statements
that provide information about the databases and tables it supports.

Oracle 5.0, Reference Manual

Search results

Summary of Contents for 5.0

Reviews:

Related manuals for 5.0

Brands by name

Popular brands