CREATE TABLE books
(
book_id bigint,
book_title varchar(100),
CONSTRAINT books_book_id_pkey PRIMARY KEY (book_id)
)WITH OIDS;
CREATE TABLE authors
(
id bigint,
book_id bigint,
author_name varchar(100),
CONSTRAINT authors_id_pkey PRIMARY KEY (id),
CONSTRAINT authors_book_id_fk FOREIGN KEY (book_id)
REFERENCES books (book_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)WITH OIDS;
INSERT INTO books (book_id,book_title) VALUES('1','PHP 5.X');
INSERT INTO books (book_id,book_title) VALUES('2','POSTGRESQL');
INSERT INTO authors (id,book_id,book_title) VALUES('1','1','Mangal Kumar');
INSERT INTO authors (id,book_id,book_title) VALUES('2','1','Pankaj Kumar');
INSERT INTO authors (id,book_id,book_title) VALUES('3','2','Brijesh Kumar');
INSERT INTO authors (id,book_id,book_title) VALUES('4','2','Mangal Kumar');
We want to select the books with their author names. We used following query which creates duplicate rows:
SELECT book_title, author_name from books JOIN authors ON (books.book_id = authors.book_id);
OUTPUT:
book_title author_name
-------------- -------------------
PHP5.X Mangal Kumar
PHP5.X Pankaj Kumar
POSTGRESQL Brijesh Kumar
POSTGRESQL Mangal Kumar
How to get the unique rows with book title and author name?