CODEWITHSUNDEEP
phpmysql
pk10
pk10

Reputation: 523

How to create a view from a table with these conditions

I have a table called BOOK_AUTHORS. It has two columns: book_id | author_name

Primary Key = (book_id + author_name)

This is because one book may have multiple authors.

I want to create a view from this where I can have book_id as the primary key and the author_name concatenated for multiple authors.

e.g. Book 'XYZ' has two authors namely 'ABC' and 'PQR'. so it has two rows in the current BOOK_AUTHORS table as shown below:

book_id | author_name
        |
XYZ     |   ABC
XYZ     |   PQR

After creating the view, I want to get this:

book_id | author_name
        |
XYZ     |   ABC, PQR

How can I achieve this?

Upvotes: 0

Views: 39

Answers (1)

Ali
Ali

Reputation: 782

You can try with 

SELECT  GROUP_CONCAT(author_name) WHERE book_id = 'XYZ';

Upvotes: 2

Related Questions