DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Message-ID;
	b=1wIZVnTKcO6kNKRzTFWFCYyIfy0JTJwLDZ1tpsT+Bqvamt5SVzCpo+Co2CwPWY5uDXZNULl5E0whBSInkHbzHbZaGGyTFWLHE5JYtwudW9uxvy/e6ngC4WzMSLR3Gbgqt3IBHlEmEjPrU0dd2QxZLQ0f1GJKm5Gz0Y/RAQfP5EQ=;
Date: Mon, 31 Dec 2007 21:25:19 -0800 (PST)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: Limit # of recs on inner join?
To: pgsql-sql@postgresql.org, Josh <josh@globalherald.net>
In-Reply-To: <alpine.LRH.1.00.0712311347330.31526@home-av-server.home-av>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Message-ID: <195966.8694.qm@web31808.mail.mud.yahoo.com>

    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