Select form table A where value in table B

Question

Table A

id    name
1     name1
2     name2
3     name3
4     name4

Table B

id    userlist
1     1,2,3,4

What I do now is

SELECT `userlist` FROM `table B` WHERE `id` = 1

Then save the result and put into second query

SELECT `name` FROM `table A` WHERE `id` in ($userlist)

Just wondering if got better way. I try

SELECT `name` FROM `table A` WHERE `id` in (SELECT `userlist` FROM `table B` WHERE `table B`.`id` = 1)

But it only return 1 result.

EDIT*

DB structure can not be change since so many data inside. I just try to optimize the old code.

having comma separated values in a single field of a table is generally a bad idea. You should think about your data representation. — Andreas Grapentin, Feb 09 '13 at 10:37
See also [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/a/3653574) — eggyal, Feb 09 '13 at 10:48
"*DB structure can not be change since so many data inside*" - why should the amount of data in the database prevent you from altering its structure? Schema changes can be effected with database queries that perform all of the restructuring for you. — eggyal, Feb 09 '13 at 10:52
@eggyal O,ok. But I need ensure all the new insert data can match this new DB structure right before change the DB? If not, it can cause an error. For now, I just optimize bit by bit without doing major change since I'm not totally understand the whole architecture — Muhammad Azizol Aminuddin, Feb 09 '13 at 11:08

score 3 · Accepted Answer · answered Feb 09 '13 at 10:43

3

You could use FIND_IN_SET:

SELECT `name`
FROM `table A` INNER JOIN `table B` ON
     FIND_IN_SET(`table A`.`id`, `table B`.`userlist`)
WHERE `table B`.`id` = 1

but i would suggest you to normalize your tables.

answered Feb 09 '13 at 10:43

fthiella

Joni · Answer 2 · 2013-02-09T10:46:34.667

2

The proper way to solve this is changing the table definition and using JOINs. For example

Table A

id    name    b_id
1     name1   1
2     name2   1
3     name3   1
4     name4   1

Table B

id    
1

To get names from table A that have B id 1 you would write:

select name from A join B on A.b_id = B.id where B.id = 1

Or, if you can't change Table A, define a new table to maintain the relationship A-B:

Table A

id    name
1     name1
2     name2
3     name3
4     name4

Table B

id    
1

Table A_B

a_id b_id
1    1
2    1
3    1
4    1

SQL to extract data:

select name from A join A_B on A.id = A_B.b_id where A_B.b_id = 1

edited Feb 09 '13 at 10:46

answered Feb 09 '13 at 10:40

Joni

Thanks for you answer. Sorry to tell. The DB structure can't be change since im not one that create it. I just try to optimize it. – Muhammad Azizol Aminuddin Feb 09 '13 at 10:44
@MuhammadAzizol tell the derp who created the tables then to improve them. Or better, hit him with something heavy :) non-normalized tables are an evil better rooted out immediately, than dealing with later. – Andreas Grapentin Feb 09 '13 at 10:47
@AndreasGrapentin I would like to. But he already left the company. Now all his work dump to me and I have to take the blame – Muhammad Azizol Aminuddin Feb 09 '13 at 10:53
@MuhammadAzizol where is the problem then? redo it, and do it right :) – Andreas Grapentin Feb 09 '13 at 12:36
@AndreasGrapentin yeah, that was my plan. But for now i need to make it abit faster since the software already live. – Muhammad Azizol Aminuddin Feb 09 '13 at 20:20

2 Answers2