Database Reference
In-Depth Information
thing for the scientific name in the column list
(
bird_families.scientific_name
). If we don't do that, MySQL would be con-
fused as to whether we want the
scientific_name
from the
birds
or the
bird_families
table. This would generate an error like this:
ERROR 1052 (23000): Column 'scientific_name' in field list is
ambiguous
You may have noticed that another new item was addedto the
SELECT
statement: the
AS
keyword. This specifies a substitute name, or
alias
, for the heading in the results set for
the column. Without the
AS
keyword for the column containing the family names, the
heading would say
bird_families.scientific_name
. That's not as attractive.
This is another style factor, but it can have more practical aspects that we'll see later. The
keyword
AS
can also be used to specify a table name like so:
SELECT
common_name
AS
'Bird'
,
families
.
scientific_name
AS
'Family'
FROM
birds
,
bird_families
AS
families
WHERE
birds
.
family_id
=
families
.
family_id
AND
order_id
=
102
AND
common_name
!=
''
ORDER BY
common_name
LIMIT
10
;
In this example, we provided an alias for the
bird_families
table. We set it to the
shorter name
families
. Note that aliases for table names must not be in quotes.
After setting the alias, we must use it wherever we want to refer to the table. So we have
to change the column selected in the field list from
bird_families.scientific_name
to
families.scientific_name
. We
also have to change the column name
bird_families.family_id
in the
WHERE
clause to
families.family_id
. If we don't make this final change, we'll get the fol-
lowing error:
ERROR 1054 (42S22):
Unknown column 'bird_families.family_id' in 'where clause'
Let's add a third table to the previous SQL statement, to get the name of the order of birds
to which the birds belong. You can do that by entering this SQL statement on your server:
SELECT common_name AS 'Bird',
families.scientific_name AS 'Family',
orders.scientific_name AS 'Order'
FROM birds, bird_families AS families, bird_orders AS orders
WHERE birds.family_id = families.family_id
AND families.order_id = orders.order_id
