Create a subquery using IN

Question

I have a question about creating a good subquery for this problem

Let's say we have table users and links.

users
+++++++++
id name
1  name1
2  name2
3  name3
+++++++++

---

links
+++++++++
id link
1  link1
2  link1
3  link1
+++++++++

And let's say I have a relationship table like this

name_links
++++++++++++
uid  lid
1    1,3
2    1,2,3
3    2
++++++++++++

I know that this is not the most used method for this. I posted some more questions about suggestions for this method. If you have any suggestions, here is the link.

mysql regular relationship table for the associated realtionship table

But using this scheme, I cannot get the request to work. The query I came up with is the following:

SELECT link 
FROM links 
WHERE links.id
    IN
    (
        SELECT lid
        FROM name_links
        WHERE uid=1
    )

I am doing this with the correct syntax and quotation marks, but I only get one line, the first line. So, for example, using the same request, I get only link1. I need to get link1 and link3.

It drives me crazy, I really need help with this.

Thank!

Answer 1

SELECT  link
FROM    name_links
JOIN    links
ON      FIND_IN_SET(id, lid)
WHERE   uid = 1

, FIND_IN_SET .