mysql question

[4u]

Hi! I am new bird in mysql,anybody help.
I have three table.there are person,song,likes.
Table person is described by a PersonID,LastName and FirstName. Table song include songid,songtitle,artistname and recordconame. Table likes show which person likes which song.
the question is:
1.List all persons who like the name song as personId 2?
2.Find the most popular songId.

(write SQL Select statement)

[mushroom]

Mysql has many programing langage interfaces, you failed the mention which one you are using.
But you will need to use "count(distinct field)" as part of your query (#2),

[godzilla]

Originally Posted by 4u

Hi! I am new bird in mysql,anybody help.
I have three table.there are person,song,likes.
Table person is described by a PersonID,LastName and FirstName. Table song include songid,songtitle,artistname and recordconame. Table likes show which person likes which song.
the question is:
1.List all persons who like the name song as personId 2?
2.Find the most popular songId.

(write SQL Select statement)

1)Do you want to get person IDs for a given song?
if yer then it is simple:
SELECT personId
FROM person NATURAL JOIN likes NATURAL JOIN song
WHERE songId='sometitle'

2)SELECT songId FROM(
SELECT songId, Count(personID) as popularity
FROM song NATURAL JOIN likes
GROUP by songId)
WHERE Popularity = MAX(popularity)

if previous does not work a simpler but less elegant answer would be:
SELECT songId, Count(personID) as popularity
FROM song NATURAL JOIN likes
GROUP by songId
ORDER by popularity DESC LIMIT 1

or even simpler
SELECT songId, Count(personID) as popularity
FROM song NATURAL JOIN likes
GROUP by songId
ORDER by popularity DESC

and the first record is what you want


In general note that if the version of MySQL that you have will complain about NATURAL word just use INNER