Limit # of recs on inner join?

From: Josh <josh(at)globalherald(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Limit # of recs on inner join?
Date: 2007-12-31 18:52:03
Message-ID: alpine.LRH.1.00.0712311347330.31526@home-av-server.home-av
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


I want to limit the number of records returned by an inner join.

Suppose I have a table of Books:

book_id
title

And, a table of authors:

book_id
author_name

Now, suppose I want to get book + author, but I only want one author for
books with multiple authors. Traditionally, I'd do something like:

select books.book_id, books.title, authors.author_name
from books
inner join authors on authors.book_id = books.book_id
where books.book_id = ?

This might return:

1 A Cat In The Hat Dr. Seuss
1 A Cat In The Hat Dr. Seuss' Partner

Instead, I just want:

1 A Cat In The Hat Dr. Seuss

How can I limit the inner join?

Cheers,
-J

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Rodrigo E. De León Plicet 2008-01-01 02:07:05 Re: Limit # of recs on inner join?
Previous Message Trilok Kumar 2007-12-31 13:53:18 Re: sql query question ?