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?



Thanks for any hints,
B.

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

Yes, that did it. Many thanks. I was stuck under the mistaken
assumption that I had to have a WHERE clause in there someplace.
Thanks again.

B.

On Wed, 26 Sep 2007 09:05:01 +0200, "Rik Wasmus"
<luiheidsgoeroe@hotmail.com> wrote:

>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'