Re: Please Help With SQL

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 Tue Mar 12 2002 - 17:52:01 CST