SQL: How to use comma values to join another table?

Question

Table A:

id  username  like_books 
-----------------------
1   Peter     1,2,3
2   John      1,2

Table B:

id  book_name  
-----------------------
1   Legend
2   StackOverFlow
3   Google

For now, Is there any sql statement that can show the output as below:

Output:

id username    like_books
------------------------------------
1  Peter       Legend,StackOverFlow,Google
2  John        Legend,StackOverFlow

Thanks.

You don't normally use a comma-separated list in a database field to join to another database field. Many values per field break the [first normal form](https://en.wikipedia.org/wiki/First_normal_form). Reasonable database designs usually replace it [with an additional table](http://www.databaseprimer.com/pages/relationship_1tox/). — 9000, Nov 20 '15 at 03:57
@user3789802: follow the second link. In your case I'd have table `user(id, name)`, table `book(id, name)` and table `user_likes_book(user_id foreign key user(id), book_id foreign key book(id))`. This is a classic [M:N or many-to-many relationship pattern](http://en.tekstenuitleg.net/articles/software/database-design-tutorial/many-to-many.html) — 9000, Nov 20 '15 at 04:11

Rahul Tripathi · Accepted Answer · 2015-11-20T04:00:13.940

3

Try this:

SELECT  a.id,a.username,
        GROUP_CONCAT(b.book_name ORDER BY b.id) like_books
FROM    A a
INNER JOIN B b ON FIND_IN_SET(b.id, a.like_books) > 0
GROUP   BY a.id

FIDDLE DEMO

edited Nov 20 '15 at 04:00

answered Nov 20 '15 at 03:54

Rahul Tripathi

152,732
28
233
299

What would be the query in SQL? – Metaphor Nov 20 '15 at 04:05
1

Thank you, it is work. But is there any better ways to design database and pattern? As @9000 said. – user3789802 Nov 20 '15 at 04:06
@Metaphor:- This is SQL only, which will work in MySQL database. – Rahul Tripathi Nov 20 '15 at 04:06
@RahulTripathi But, i've got error in SQL 2012. – Metaphor Nov 20 '15 at 04:08
There is nothing like SQL 2012. You might be trying in SQL Server...right? – Rahul Tripathi Nov 20 '15 at 04:08
good answer can you explain the query so that new learns like me can understand the flow.mainly GROUP_CONCAT,FIND_IN_SET? – Vigneswaran S Nov 20 '15 at 04:08
Ya, SQL Server 2012. the error: 'GROUP_CONCAT is not a recognized function name' – Metaphor Nov 20 '15 at 04:10
@Metaphor:- Group_Concat is for MySQL. You can use STUFF function in SQL Server – Rahul Tripathi Nov 20 '15 at 04:24
Thanks a lot, got it. – Metaphor Nov 20 '15 at 04:25
@VigneswaranS:- You can refer: [GROUP_CONCAT](http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat) and [FIND_IN_SET](https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set) Do let me know if you dont understand anything in it. – Rahul Tripathi Nov 20 '15 at 04:25
@Metaphor:- You are welcome! – Rahul Tripathi Nov 20 '15 at 04:26

SQL: How to use comma values to join another table?

1 Answers1