Re: Function to Pivot data

You should use a LEFT OUTER JOIN on the table if you are not getting
anything because of a NULL. This will return the book and it will return
NULL values in the fields that have no information.

Darren Ferguson

On Thu, 31 Jan 2002, Ellen Cyran wrote:

> Yes, the other bookid check was missing.  I only have two problems now.
>
> 1.  I don't always have 2 authors, if I only have 1 then I don't
> get that book at all.
> 2.  I can't be sure what the maximum number of authors is either.  I could
> of course make the maximum pretty large, but then it does become
> somewhat tedious to code the SQL statement.  Could this be easily made into a
> function where the maximum authors is passed to it?
>
> Thanks for the help.
>
>
> At 01:22 PM 01/31/2002 -0800, Stephan Szabo wrote:
> >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;
> >
> >Shouldn't you be checking a.bookid=e.bookid as well or am I missing
> >something?
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>