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

You seem to have a CARTESIAN JOIN in the query from what i can see anyway

a -> d
b -> d
c -> e

There are two views (i don't remember correct syntax) that are not joined
so you will

A) Get duplicates
B) Get the wrong information

Try making sure that all of your tables are linked

Darren Ferguson

On Thu, 31 Jan 2002, Ellen Cyran wrote:

> 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
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>