Re: Function to Pivot data

I've run the SQL statement below and it doesn't give me
what I thought and even gives me some incorrect data.
Any idea why?

Here's my version of the statement:

SELECT a.title, b.Author AS auth1, c.author AS auth2
FROM book AS a, tbl_author AS b, tbl_author AS c, author_book AS d,
author_book AS e
WHERE a.bookID=d.bookID And b.authorID=d.authorid And d.auth_rank=1 And
c.authorID=e.authorid And e.auth_rank=2;

Here's the tables:

tbl_author:
author authorid
brown 1
jones 2
smith 3

Author_Book:
bookid authorid auth_rank
2 1 1
1 2 1
2 2 2
3 2 1
3 3 2
1 3 2

title bookid
book1 1
book2 2
book3 3

Here's the output:
title auth1 auth2
book2 brown jones
book2 brown smith
book2 brown smith
book1 jones jones
book1 jones smith
book1 jones smith
book3 jones jones
book3 jones smith
book3 jones smith

Book2 should only be brown and jones not brown and smith. Also, is there a
way to eliminate
the jones and jones for book1 and book3? I can easily get rid of the
duplicates using distinct
so that shouldn't be a problem.

Ellen
-----
At 02:49 PM 01/31/2002 -0500, Andrew Sullivan wrote:
>On Thu, Jan 31, 2002 at 11:43:37AM -0500, Ellen Cyran wrote:
>>
>> A few of the tables in a normalized database would be:
>> Author:
>> AuthorID, LastName, FirstName, DepartmentID
>
>> Author_Book:
>> AuthorID, BookID
>
>
>Seems to me like the author_book table will need a field which
>indicates "first author", "second author", &c. You can't just sort
>alphabetically, because that might not be the correct precedence. So
>you need something like authorno (probably NOT NULL DEFAULT 1, but
>you'll have to do some extra work to make sure that you never have a
>book with more than one 1st author, 2d author, &c.).
>
>> Title, Date, Author1, Author2, Author3, Author4
>>
>> Where Author1, Author2, etc. are FirstName + LastName.
>
>I'm not an expert in designing this sort of thing, and someone is
>going to choke when s/he sees what a horribly inefficient way this
>works (if I gave it more thought, I could probably come up with a
>better answer). Still, this would work in case you have a known
>number of authors for every book:
>
>SELECT a.title,b.firstname||' '||b.lastname AS auth1, c.firstname||'
>'||c.lastname AS auth2, a.pubdate FROM books AS a, author AS b,
>author AS c, author_books AS d, author_books AS e WHERE
>a.id=d.book_id AND b.id=d.auth_id AND d.auth_rank=1 AND
>c.id=e.auth_id AND e.auth_rank = 2;
>
>I doubt you'll have that case, though, and you'd have to add some
>LEFT JOINs to the mix. For any amount of data at all, you'll have a
>performance problem.
>
>But I wonder if the difficulty might be because you're trying to
>normalise a simple one-to-many relation, and you actually have a
>one-to-many relation which has order in the "many" side. That's a
>different problem, really, and probably needs something like a
>unified book-author table with the book information in it:
>
>CREATE TABLE book (title text,
> pubdate date,
> author1 int4,
> author2 int4,
> author3 int4 . . .
>
>The trouble in this case is that you'll be limited to some maximim
>number of authors. (This is one problem the MARC cataloguing
>standard gets around. But I think that's rather more complicated
>that you want.)
>
>The author info could still be made separate, and references could be
>put in the book table so that if the author's info changed, you could
>change it for every book entry in one go.
>
>A
>
>--
>----
>Andrew Sullivan 87 Mowat Avenue
>Liberty RMS Toronto, Ontario Canada
><andrew(at)libertyrms(dot)info> M6K 3E3
> +1 416 646 3304 x110
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>