Re: Please Help With SQL

SELECT bandid, COUNT(*)
FROM albums
GROUP BY bandid
HAVING COUNT(*) = 2; will give you the band ids in there more than once. So using it as part of an inline view as in:

SELECT bandid
FROM (SELECT bandid, COUNT(*)
FROM albums
GROUP BY bandid
HAVING COUNT(*) = 2); will give you the Band Ids. Which when put into a subquery will give you the result:

SELECT bandname
FROM bands
WHERE bandid IN (

   SELECT bandid
   FROM (

      SELECT bandid, COUNT(*)
      FROM albums
      GROUP BY bandid
      HAVING COUNT(*) = 2));

Will get you the result.

Are there other ways? Yep! Lots of them. But I like this one this afternoon.

Daniel Morgan

Alison Pont wrote:

> Table Bands with columns:
> BandId, BandName
>
> Table Albums with columns:
> AId, BandId, Title, YearProduced
>
> How do find all bands that recorded TWO albums in the same year? I want to
> output BandName and the names of each of two albums (the Tiltles) in the
> same record.
>
> eg.
> BandName Title Title YearProduced
> ----------------------------------------------------------
> Bread BestHits1 BestHits2 1982
> James Last Classic1 Classics2 1988
>
> Thanks in advance.
Received on Tue Mar 12 2002 - 17:00:50 CST