Thread: Limit # of recs on inner join?

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

On Dec 31, 2007 1:52 PM, Josh <josh@globalherald.net> wrote:
> Instead, I just want:
>
> 1       A Cat In The Hat        Dr. Seuss

SELECT books.book_id, books.title,      (SELECT author_name       FROM   authors       WHERE  book_id = books.book_id
   LIMIT 1) AS author_name
 
FROM   books
WHERE  books.book_id = ?

    SELECT DISTINCT ON ( B.book_id, B.title ) B.book_id, B.title, A.Author     FROM Books AS B
INNER JOIN Authors AS A       ON A.book_id = B.book_id;


--- On Mon, 12/31/07, Josh <josh@globalherald.net> wrote:

> From: Josh <josh@globalherald.net>
> Subject: [SQL] Limit # of recs on inner join?
> To: pgsql-sql@postgresql.org
> Date: Monday, December 31, 2007, 10:52 AM
> 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
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map
> settings