From: | Richard Broersma Jr <rabroersma(at)yahoo(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org, Josh <josh(at)globalherald(dot)net> |
Subject: | Re: Limit # of recs on inner join? |
Date: | 2008-01-01 05:25:19 |
Message-ID: | 195966.8694.qm@web31808.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)globalherald(dot)net> wrote:
> From: Josh <josh(at)globalherald(dot)net>
> Subject: [SQL] Limit # of recs on inner join?
> To: pgsql-sql(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Mario Becroft | 2008-01-01 05:51:30 | Using rules to implement backward-compatible schema changes |
Previous Message | Rodrigo E. De León Plicet | 2008-01-01 02:07:05 | Re: Limit # of recs on inner join? |