how to combine sql tables into non repetitive inputs-CodePudding

I want to get repetitive titles to have name, name and not be repeated in another Colum.

#CODE

create or replace view Master_view as
select books.title, books.isbn as book_id, authors.name, authors.e_mail as author_id
from book_author join books on book_author.book_id = books.id
join authors on book_author.author_id = authors.id;

#OUTPUT

               title               | book_id |           name            |           author_id            
----------------------------------- --------- --------------------------- --------------------------------
 The Pragmatic Programmer          |  999999 | Andrew Hunt               | [email protected]
 The Pragmatic Programmer          |  999999 | Dave Thomas               | [email protected]
 Pragmatic Thinking and Learning   |  999998 | Andrew Hunt               | [email protected]
 Pragmatic Unit Testing            |  999997 | Andrew Hunt               | [email protected]
 Pragmatic Unit Testing            |  999997 | Dave Thomas               | [email protected]
 Agile Web Development with Rails  |  999996 | Dave Thomas               | [email protected]
 Agile Web Development with Rails  |  999996 | Sam Ruby                  | [email protected]
 Agile Web Development with Rails  |  999996 | David Heinemeier Hansson  | [email protected]
(8 rows)

CodePudding user response：

create or replace view Master_view as
select books.title, books.isbn as book_id, array_agg(authors.name), array_agg(authors.e_mail) as author_id
from book_author join books on book_author.book_id = books.id
join authors on book_author.author_id = authors.id group by (books.title, books.isbn);

This will give you the expected results.You have to group the rows using isbn and title of the book.