title: Subquery returns more than 1 row, but I want it to
body: <p>I'm trying to run a query on my music database.</p>
  
  <p>The relevant part of my database looks like this:</p>
  
  <pre><code>CREATE TABLE ARTIST (
  artistid    integer(3)  auto_increment  not null,
  name    varchar(30) not null,
  //other columns
  CONSTRAINT ALBUM_pk PRIMARY KEY (artistid)
  );
  
  CREATE TABLE SONG (
  songid  integer(5)  auto_increment  not null,
  lineupid    integer(5)  not null,
  title   varchar(50) not null,
  //other columns
  CONSTRAINT SONG_pk PRIMARY KEY (songid),
  CONSTRAINT SONG_fk_ARTIST FOREIGN KEY (lineupid)
      REFERENCES ARTISTLINEUPLINEUP (artistlineupid),
  CONSTRAINT SONG_fk_GENRE FOREIGN KEY (genreid)
      REFERENCES GENRE (genreid)
  );
  
  CREATE TABLE ARTISTLINEUP (
  artistlineupid  integer(3)  auto_increment not null,
  lineupid    integer(5)  not null,
  artistid    integer(3)  not null, 
  CONSTRAINT ARTISTLINEUP_pk PRIMARY KEY (artistlineupid),
  CONSTRAINT ARTISTLINEUP_fk_ARTIST FOREIGN KEY (artistid)
      REFERENCES ARTIST (artistid),
  CONSTRAINT ARTISTLINEUP_fk_LINEUP FOREIGN KEY (lineupid)
      REFERENCES LINEUP (lineupid)
  );
  </code></pre>
  
  <p>I'm trying to get all the songs written by one artist, which has multiple lineups.</p>
  
  <pre><code>SELECT title
  FROM song
  WHERE lineupid = (
      SELECT artistlineupid
      FROM artistlineup
      WHERE artistid = (
          SELECT artistid
          FROM artist
          WHERE name = "Black Sabbath"));
  </code></pre>
  
  <p>However, it gives me the following error:</p>
  
  <pre><code>#1242 - Subquery returns more than 1 row
  </code></pre>
  
  <p>This is correct, because this artist has multiple lineups and therefore returns two numbers. Why can't it return the songs with both lineupid's? How can I get this to work?</p>
  
  <p>Thanks</p>