MySQL query, record not selecting proper with IN clause

Question

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?

Instead of executing the query, try and echo it and see if the syntax is correct. — Epodax
– Epodax, Commented Sep 8, 2015 at 9:22
@Epodax only issue is, it returns only 1 record. when i put sub-query result into IN clause, then it return me correct result — himanshu archirayan
– himanshu archirayan, Commented Sep 8, 2015 at 9:24
Your query isn't going to work as is, because you are trying to find a value in a comma-separated string. In your previous question today, you were advised to not use this database design. Now the roosters are coming home to roost. — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 8, 2015 at 9:27
@TimBiegeleisen you are right, but this database designed by other. i have only option to use it any how — himanshu archirayan
– himanshu archirayan, Commented Sep 8, 2015 at 9:28

Tim Biegeleisen · Accepted Answer · 2015-09-08 09:40:22Z

1

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

edited Sep 8, 2015 at 9:40

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

answered Sep 8, 2015 at 9:32

Aleksey Krivtsov

2911 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2015-09-08 09:41:08Z

0

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;

edited Sep 8, 2015 at 9:41

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

answered Sep 8, 2015 at 9:31

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Collectives™ on Stack Overflow

MySQL query, record not selecting proper with IN clause

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related