Skip site navigation (1) Skip section navigation (2)

Re: Function to Pivot data

From: Darren Ferguson <darren(at)crystalballinc(dot)com>
To: Ellen Cyran <ellen(at)urban(dot)csuohio(dot)edu>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Function to Pivot data
Date: 2002-01-31 22:24:11
Message-ID: Pine.LNX.4.10.10201311723150.2331-100000@thread.crystalballinc.com (view raw or flat)
Thread:
Lists: pgsql-general
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(at)postgresql(dot)org
> 


In response to

pgsql-general by date

Next:From: Tom LaneDate: 2002-01-31 22:35:26
Subject: Re: Function to Pivot data
Previous:From: omid omoomiDate: 2002-01-31 22:09:41
Subject: Re: System commands

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group