Database Reference
In-Depth Information
| Cracidae |
| Numididae |
| Odontophoridae |
| Phasianidae |
+----------------+
The inner query (i.e., the subquery here) returns one value, the
order_id
. That's used to
complete the
WHERE
clause of the outer query. That was pretty simple. Let's look at an-
other example of a scalar subquery.
We had an example earlier in this chapter, in the section related to using a
JOIN
, in which
we selected members from Russia who had sighted birds of the family
Scolopacidae
. To
thank members in Russia for using our telephone application for recording sightings,
we're going to give a one-year premium membership to one of those members. Enter this
hefty SQL statement in
mysql
:
UPDATE
humans
SET
membership_type
=
'premium'
,
membership_expiration
=
DATE_ADD
(
IFNULL
(
membership_expiration
,
CURDATE
()),
INTERVAL
1
YEAR
)
WHERE
human_id
=
(
SELECT
human_id
FROM
(
SELECT
human_id
,
COUNT
(*)
AS
sightings
,
join_date
FROM
birdwatchers
.
bird_sightings
JOIN
birdwatchers
.
humans
USING
(
human_id
)
JOIN
rookery
.
birds
USING
(
bird_id
)
JOIN
rookery
.
bird_families
USING
(
family_id
)
WHERE
country_id
=
'ru'
AND
bird_families
.
scientific_name
=
'Scolopacidae'
GROUP BY
human_id
)
AS
derived_1
WHERE
sightings
>
5
ORDER BY
join_date
ASC
LIMIT
1
);
The most inner query here is basically the same as the one in the example mentioned
earlier. The difference is that here we're not selecting the names involved. Instead, we're
selecting the
human_id
and the
join_date
(i.e., the date that the member joined).
Withthe
GROUP BY
clause, we're grouping members based on the
human_id
to get a
count with the
COUNT()
function. Put another way, we're counting the number of entries
of each
human_id
in the
bird_sightings
table for the bird family and member
country we specified. That subquery will return a table of results; it's a table subquery.
We'll talk more about that type of subquery later in this chapter.
