CODEWITHSUNDEEP
phpmysql
himanshu archirayan
himanshu archirayan

Reputation: 223

MySQL query, record not selecting proper with IN clause

here i have 2 table in database like this,

1)videos

id        name   
200119    one
200120    two
200121    three

2)sessions_to_channels

channel_id    playlist_id   videos
50            359           200119,200120,200121

I want to select all the videos.name from videos table where videos.id IN sessions_to_channels.videos.

For that i am using this query,

SELECT v.name FROM videos as v WHERE v.id in ( select videos from sessions_to_channels where playlist_id=359 and channel_id=50 )

But it return me only 1 record

id      name
200119  one

I am doing anything wrong here?

Upvotes: 2

Views: 56

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Saving comma-separated data is never a good choice and the best option is to normalize the table. This will definitely make your life easy, however for the current situation you can use find_in_set function something as

SELECT v.name
FROM videos v JOIN sessions_to_channels s
ON FIND_IN_SET(v.id,s.videos) > 0 
WHERE s.playlist_id = 359 AND s.channel_id = 50;

Upvotes: 0

Aleksey Krivtsov
Aleksey Krivtsov

Reputation: 291

If you use string in sessions_to_channels.videos, you must use FIND_IN_SET. Like

SELECT v.name
FROM videos AS v
WHERE FIND_IN_SET(v.id, (
    SELECT videos FROM sessions_to_channels WHERE playlist_id=359 and channel_id=50 )
)

Upvotes: 1

Related Questions