Help with this query dealing with aggregates

Question

I have a database that stores information about artists, bands and albums and I'm trying to select the band names and album titles for bands with more than 1 album.

band_id, name,            year_formed
1,       blink182,        1990
2,       fall out boy,    1997
3,       u2,              1982
4,       coldplay,        1990
5,       zach brown band, 2002

and album

album_id, title,                          band_id
1,        take off your pants and jacket, 1
2,        stars,                          3
3,        fbgm,                           3
4,        reach,                          3
5,        yup,                            4

This doesn't seem too hard of a problem but I cannot figure it out for the life of me. The problem seems to be with getting both the album title and band name from the select statement.

SELECT b.name, alb.title
FROM band as b, album as alb
WHERE exists (SELECT * FROM album as al WHERE b.bid = alb.bid)

Returns the all band names and their album titles. My guess is to have the subquery handle finding the bands with more than one album but I just am at a complete loss for how to do this. Subqueries have been throwing me for a loop in general, so any nice description of how they work conceptually would be appreciated.

Answer 1

To determine which bands that have more than 1 album you can do something like:

SELECT b.bid 
FROM band as b
JOIN album as a
   ON b.bid = a.bid
GROUP BY b.bid
HAVING COUNT(*) > 1

Since band_name and year_formed is likely to be f.d. of bid:

SELECT b.bid, max(b.name), max(b.year_formed) 
FROM band as b
JOIN album as a
   ON b.bid = a.bid
GROUP BY b.bid
HAVING COUNT(*) > 1 

Answer 2

First your query is missing a join on the two tables involved. After that, it can be slightly altered to get you the albums of bands with more than one album, like this:

SELECT b.name, alb.title                     -- show me all bands 
FROM band AS b                               -- and their albums
  JOIN album AS alb                          
    ON alb.band_id = b.band_id    
WHERE EXISTS                                 -- where exists at least one
      ( SELECT *    
        FROM album AS al                     -- other album
        WHERE al.band_id = alb.band_id       -- of the same band
          AND al.album_id <> alb.album_id    -- (this ensures the "other")
      ) ;