By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

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

P: 2
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?
Apr 9 '10 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 700
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?
Apr 9 '10 #2

P: 2
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:
Apr 10 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.