pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLimit # of recs on inner join?
3+ messages / 3 participants
[nested] [flat]
* Limit # of recs on inner join?
@ 2007-12-31 18:52 Josh <josh@globalherald.net>
0 siblings, 2 replies; 3+ messages in thread
From: Josh @ 2007-12-31 18:52 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Limit # of recs on inner join?
@ 2008-01-01 02:07 Rodrigo E. De León Plicet <rdeleonp@gmail.com>
parent: Josh <josh@globalherald.net>
1 sibling, 0 replies; 3+ messages in thread
From: Rodrigo E. De León Plicet @ 2008-01-01 02:07 UTC (permalink / raw)
To: Josh <josh@globalherald.net>; +Cc: pgsql-sql
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 = ?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Limit # of recs on inner join?
@ 2008-01-01 05:25 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Josh <josh@globalherald.net>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Broersma Jr @ 2008-01-01 05:25 UTC (permalink / raw)
To: pgsql-sql@postgresql.org, Josh <josh@globalherald.net>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-01-01 05:25 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-31 18:52 Limit # of recs on inner join? Josh <josh@globalherald.net>
2008-01-01 02:07 ` Rodrigo E. De León Plicet <rdeleonp@gmail.com>
2008-01-01 05:25 ` Richard Broersma Jr <rabroersma@yahoo.com>
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