1

I am using mysql database.
I have two table one is user_songs and second is user_like

Fields of user_song:

id(auto incement)
song_id
user_id
song_name
song_file

Fields of user_like:

id(auto incement)
song_id
uder_id
like

In user_like I am saving data where any user going like song so each row contain data like:

1 1 1 1

Please note like saves either 0 or 1.0 for dislike and 1 for like.

Now I need to find out top 20 most like songs id from user_like and get all data from user_songs from user_songs.

How to do this?
Thanks in advance to spend your valuable time to solve this query.

John Woo
  • 258,903
  • 69
  • 498
  • 492
mayur bhagat
  • 209
  • 3
  • 12

1 Answers1

0
SELECT  a.*, b.totalLike
FROM    user_song a
        INNER JOIN
        (
            SELECT  song_id, COUNT(*) totalLike
            FROM    user_like
            GROUP   song_id
        ) b ON a.song_id = b.song_ID
ORDER BY totalLike DESC
LIMIT 20

Caution: the query above doesn't handle tie up values on totaluserLike result. It will only display 20 records on the result list.

John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Hi ,Really nice.Inline view perfect suits here .Thanks for answer.My English is week so can you please explain what do you mean by "the query above doesn't handle tie up values on totaluserLike result".Please explain.Thanks for valuable time. – mayur bhagat Jan 17 '13 at 11:23
  • $rs=mysql_query("SELECT a.* FROM atr_songs a INNER JOIN ( SELECT song_id ,count(*) totallike FROM atr_like where 'like'=1 GROUP by song_id ) b ON a.song_id = b.song_ID ORDER BY totalLike DESC LIMIT 20 ") or die(mysql_error()); while($obj = mysql_fetch_object($rs)) { $arr[] = $obj; } echo json_encode($arr); ?> – mayur bhagat Jan 18 '13 at 11:52
  • @mayurbhagat in you condition, `where 'like'=1` what is `LIKE` a column or an `ALIAS` of a column? and `LIKE` is a reserved keyword. – John Woo Jan 18 '13 at 11:53
  • @ jW I make it 'like' as per your comment of my second question..like is column name of table atr_like http://stackoverflow.com/questions/14395167/getting-syntax-error-in-mysql-php-you-have-an-error-in-your-sql-syntax – mayur bhagat Jan 18 '13 at 12:00
  • 1
    use backtick not single quote. `:)` – John Woo Jan 18 '13 at 12:06
  • 1
    How stupid I am!really thank you so much I could not solve my both problem without your help.Actually I used back tick first time today in last 10 year.. :) – mayur bhagat Jan 18 '13 at 12:12