How to join to get unique values from tables, if table have one to many relation.

[mangalv]

CREATE TABLE books
(
book_id bigint,
book_title varchar(100),
CONSTRAINT books_book_id_p key 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_t itle) VALUES('1','PHP 5.X');
INSERT INTO books (book_id,book_t itle) VALUES('2','POS TGRESQL');

INSERT INTO authors (id,book_id,boo k_title) VALUES('1','1', 'Mangal Kumar');
INSERT INTO authors (id,book_id,boo k_title) VALUES('2','1', 'Pankaj Kumar');
INSERT INTO authors (id,book_id,boo k_title) VALUES('3','2', 'Brijesh Kumar');
INSERT INTO authors (id,book_id,boo k_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?

[rski]

But the rows you put are unique?
I don't get what is your problem.
To get unique rows you can use DISTINCT, but in your example you don't need it.
How do you want the output of the query look like?

[mangalv]

CHECK THIS QUERY AND ITS OUTPUT:

SELECT DISTINCT books.book_id, book_title, author_name from books JOIN authors ON (books.book_id = authors.book_id ) ORDER BY author_name:

OUTPUT:

book_id book_title author_name
-------------- -------------- ---------------------------
2 PHP5.X Brijesh Kumar
1 PHP5.X Mangal Kumar
2 POSTGRESQL Mangal Kumar
1 POSTGRESQL Pankaj Kumar

In the above output book_id is repeating, we want that query return unique ids only: