Re: Function to Pivot data - Mailing list pgsql-general

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@libertyrms.info>                              M6K 3E3
>                                         +1 416 646 3304 x110
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>