On Wed, 26 Sep 2007 05:44:40 +0200, Bryan <wendelnNOSPAMMING@gmail.com>
wrote:

> Hello all,
>
> A mysql newbie here with a query-building question that seems simple,
> but I can't get the syntax right.
>
> Say that I have three tables with the following columns...
>
>
> Songs:
> -----
> song_id
> song_title
>
>
> Singers:
> -------
> singer_id
> first_name
>
>
> Singers_in_Songs:
> ----------------
> id
> song_id
> singer_id
>
> (the Singers_in_Songs join table allowing for normalization: one
> singer could have many songs, and one song could have many singers
> performing in it.)
>
> I know the SELECT to find all the songs featuring a certain singer,
> e.g.:
>
> SELECT song_title FROM Songs JOIN Singers_in_Songs USING (song_id)
> JOIN Singer USING (singer_id) WHERE Singers.first_name = 'Lucy';
>
> My question is: How do I modify this query to get all song titles in
> which 'Lucy' AND 'Linus' are singing, but ONLY those titles in which
> they BOTH appear?

Perhaps something like this (untested):

SELECT songs.id, songs.song_title
FROM songs
JOIN singers_in_songs sis1
ON sis1.song_id = songs.id
JOIN singers s1
ON s1.id = sis1.singer_id
AND s1.first_name = 'Lucy'
JOIN singers_in_songs sis2
ON sis2.song_id = songs.id
JOIN singers s2
ON s2.id = sis2.singer_id
AND s2.first_name = 'Linus'
--
Rik Wasmus