In the below example, the book titled 'Collapse of the Dollar' has multiple authors.
create table books
(surrId number(5),
book_id number(7),
isbn number (10),
title varchar2(100) ,
Author varchar2(100)
);
insert into books values (1, 457, 8478, 'Perilous Power' , 'Noam Chomsky');
insert into books values (2, 458, 2345, 'Macbeth' , 'Shakespeare');
insert into books values (3, 459, 6789, 'Collapse of the Dollar' , 'James Turk');
insert into books values (4, 459, 6789, 'Collapse of the Dollar' , 'John Rubino');
col title format a35
col author format a15
col title format a15
set lines 200
SQL> select * from books;
SURRID BOOK_ID ISBN TITLE AUTHOR
---------- ---------- ---------- ----------------------------------- ---------------
1 457 8478 Perilous Power Noam Chomsky
2 458 2345 Macbeth Shakespeare
3 459 6789 Collapse of the Dollar James Turk
4 459 6789 Collapse of the Dollar John Rubino
I need to write a query whiich returns book details but it should identify records (ie. Titles) with multiple authors and return the record with Authors separated with pipe ('|') like (no need of SURRID column )
expected output
-- no need to retrieve surrogate ID
BOOK_ID ISBN TITLE AUTHOR
--------- ---------- ----------------------------------- ---------------
457 8478 Perilous Power Noam Chomsky
458 2345 Macbeth Shakespeare
459 6789 Collapse of the Dollar James Turk|John Rubino
Related question on the above Table design (Create table DDL shown above):
A table storing book details can only be designed like above. Right ? I mean, the duplication of records for one book because of multiple authors cannot be avoided. Right ?
One wonders how Amazon has desined its books table :)