0

I want to integrate like functionality in iOS/android app. I am developing service for that. My question is : I have table called songs in which I have song details

**like**

song
song_id
station_id
song_file

so on. I have second table called userlike where user like inforamation are stored.

**userlike**
user_id
song_id
like

My main problem is that I want to return all song details with like value information for particular user.If uer has not like it then I may send 'zero' or null in my JSON. How to build this query? I need song data and like value if user has liked it.

mayur bhagat
  • 209
  • 3
  • 12

4 Answers4

0

try this

select s.song, s.song_id from song s , userlike u where u.user_id = your userId;
PSR
  • 39,804
  • 41
  • 111
  • 151
0

assuming userlike.like is boolean value

SELECT like.song, like.song_id,
 like.station_id,
 like.song_file,
IFNULL(userlike.like,0)
from like left join userlike on like.song_id=userlike.song_id
and userlike.user_id=/*your userid*/
rjdmello
  • 865
  • 2
  • 9
  • 14
0

Sounds like you need to use an OUTER JOIN:

SELECT s.song_id, s.song, u.like
FROM song s 
    LEFT JOIN userlike u 
        ON s.song_id = u.song_id 
            AND u.user_id = @userId

Putting the userId in the Join will ensure the song is returned even if the user hasn't liked it.

sgeddes
  • 62,311
  • 6
  • 61
  • 83
0
select songs.song_id as song_id , songs.station_id as station_id , songs.song_file as song_file , userlike.user_id as user_id ,  COALESCE(userlike.like, 0) as like 
            FROM songs LEFT JOIN userlike
            on songs.song_id = userlike.song_id
            and userlike.user_id = 'your particular user'

The above sql should work. Please look for table name and column names for correction.

Thanks

Piyas De
  • 1,786
  • 15
  • 27