Creating subquery using IN

Question

I have a question on making a good subquery for this problem

Let's say we have a table users, and links.

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

---

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

And lets say i have a relationship table like this

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

I know this is not the most used method to do this. I posted another questions on suggestions on this method. If you have any suggestions here is the link.

http://stackoverflow.com/questions/2349216/mysql-query-normal-relationship-table-vs-concatenated-realtionship-table

But using this scheme, i can't get the query 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 right syntax and quotes, but i only get one row, the first row. So for example using this same query i only get link1 as result. I need to get link1 and link3.

This is driving me nuts, i can really need some help with this.

Thanks!

Answer 1

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

As I mentioned in the answer to your previous question, FIND_IN_SET is unsargable.