From: Richard Broersma Jr <rabroersma@yahoo.com>
To: pgsql-sql@postgresql.org, Josh <josh@globalherald.net>
Subject: Re: Limit # of recs on inner join?
Date: Mon, 31 Dec 2007 21:25:19 -0800 (PST)
Message-ID: <195966.8694.qm@web31808.mail.mud.yahoo.com> (raw)
In-Reply-To: <alpine.LRH.1.00.0712311347330.31526@home-av-server.home-av>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rabroersma@yahoo.com, josh@globalherald.net
Subject: Re: Limit # of recs on inner join?
In-Reply-To: <195966.8694.qm@web31808.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox