Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> 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
![]() |
![]() |