Why wont this work?
SELECT BANDID, COUNT(TITLES)
FROM ALBUMS
GROUP BY BANDID
HAVING COUNT(TITLES) = 2;
"damorgan" <damorgan_at_exesolutions.com> wrote in message
news:3C8E88A2.87A9B9B3_at_exesolutions.com...
> 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 - 16:22:35 CST
