Re: Not quite managing my first join

On Apr 9, 2005 9:39 PM, Todd Lewis <lewis-todd(at)sbcglobal(dot)net> wrote:
>
> Is there anything in postgre like the NVL() function in Oracle?
>
> Bob Henkel wrote:
>
> >
> >
> > On Apr 9, 2005 4:47 PM, *Bob Henkel* <luckyratfoot(at)gmail(dot)com
> > <mailto:luckyratfoot(at)gmail(dot)com>> wrote:
> >
> >
> >
> > On Apr 9, 2005 3:49 PM, *Rodolfo J. Paiz* <rpaiz(at)simpaticus(dot)com
> > <mailto:rpaiz(at)simpaticus(dot)com>> wrote:
> >
> > Hi!
> >
> > I'm within a hair of getting my first join to work, but
> > something is
> > wrong and for the life of me I can't figure out why. Hints,
> > anyone?
> >
> > ====================================================================
> > select t1.month,t1.single,t2.multi from (
> > select to_char(date, 'YYYY-MM') as month, sum(hrs_total) as
> > single
> > from flights,aircraft
> > where flights.callsign=aircraft.callsign and
> > aircraft.engines=1
> > group by month order by month) as t1
> > full outer join (
> > select to_char(date, 'YYYY-MM') as month, sum(hrs_total) as
> > multi
> > from flights,aircraft
> > where flights.callsign=aircraft.callsign and
> > aircraft.engines=2
> > group by month order by month) as t2
> > on (t1.month = t2.month);
> >
> > month | single | multi
> > ---------+--------+-------
> > 2003-08 | 5.6 |
> > 2003-09 | 3.5 |
> > 2003-10 | 4.2 |
> > 2003-11 | 17.7 |
> > 2003-12 | 16.4 |
> > 2004-01 | 13.3 |
> > 2004-02 | 1.7 | 7.6
> > 2004-03 | 12.2 |
> > 2004-04 | 13.3 | 4.1
> > 2004-05 | 7.3 |
> > 2004-06 | 7.1 | 9.4
> > 2004-07 | 2.8 | 7.6
> > 2004-08 | 6.0 | 7.0
> > 2004-09 | 7.9 | 2.4
> > 2004-10 | 2.5 |
> > | | 17.4
> > 2004-12 | 5.8 | 3.1
> > | | 10.1
> > | | 14.1
> > 2005-03 | 9.5 | 2.7
> > (20 rows)
> > ====================================================================
> >
> > The problem is that the query seems to be returning all the
> > correct
> > results, *BUT* I have two problems:
> >
> > - Instead of zero where I did not fly a multi-engine airplane, it
> > shows no value at all.
> >
> > - For columns where t1 had no value, no month is shown.
> >
> > How can I fix those two little details? My brain is
> > temporarily fried
> > and I'm not understanding the docs very clearly.
> >
> > Thanks!
> >
> > --
> > Rodolfo J. Paiz <rpaiz(at)simpaticus(dot)com
> > <mailto:rpaiz(at)simpaticus(dot)com>>
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
> > Well it looks like your outer join is doing exactly like it should.
> > >From the docs at
> >
> http://www.postgresql.org/docs/8.0/interactive/queries-table-expressions.html
> >
> > "FULL OUTER JOIN
> >
> > First, an inner join is performed. Then, for each row in T1
> > that does not satisfy the join condition with any row in T2, a
> > joined row is added with null values in columns of T2. Also,
> > for each row of T2 that does not satisfy the join condition
> > with any row in T1, a joined row with null values in the
> > columns of T1 is added."
> >
> > Here is a simple example that shows your problem and how you can
> > turn those nulls into something
> >
> > CREATE TABLE child
> > (
> > child_id int8,
> > child_name varchar(50)
> > ) ;
> >
> > CREATE TABLE parent
> > (
> > parent_id int8,
> > parent_name varchar(50),
> > child_id int8
> > ) ;
> >
> >
> > insert into parent values (1,'Mommy Kim',89);--HAS A CHILD
> > insert into parent values (1,'Mommy Lisa',NULL);--HAS NO CHILD
> > insert into child values (89,'Child Chucky');--HAS A MOTHER
> > insert into child values (109,'Child Bruce');--HAS NO MOTHER
> >
> >
> > SELECT p.parent_name,c.child_name
> > FROM public.parent as p
> > FULL JOIN public.child as c ON p.child_id = c.child_id;
> >
> > SELECT CASE WHEN p.parent_name IS NULL
> > THEN 'HAS NO CHILD' ELSE p.parent_name
> > END,
> > CASE WHEN c.child_name IS NULL
> > THEN 'HAS NO PARENT' ELSE c.child_name
> > END
> > FROM public.parent as p
> > FULL JOIN public.child as c ON p.child_id = c.child_id;
> >
> > Not that this is a big deal, but the has no parent and has no child
> > strings should be switched so it reads correctly.
>
> Try this->
http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html
9.13.2. COALESCE

COALESCE(*value* [, ...])

The COALESCE function returns the first of its arguments that is not null.
Null is returned only if all arguments are null. This is often useful to
substitute a default value for null values when data is retrieved for
display, for example:

SELECT COALESCE(description, short_description, '(none)') ...

Like a CASE expression, COALESCE will not evaluate arguments that are not
needed to determine the result; that is, arguments to the right of the first
non-null argument are not evaluated.
9.13.3.