This has "student assignment" written all over it, and everyone provided an
answer...
"damorgan" <damorgan_at_exesolutions.com> wrote in message
news:3C8E94A0.FA32055B_at_exesolutions.com...
> It only gives the bandid but not the name. I interpreted "How do find all
bands"
> as being the band names, not their ids.
>
> Daniel Morgan
>
>
>
> Ryan Gaffuri wrote:
>
> > 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 Wed Mar 13 2002 - 08:15:11 CST
