Re: Please Help With SQL

You are correct. I wish I'd caught that.

Daniel Morgan

Alan wrote:

> 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 - 10:01:47 CST