Thread: Not quite managing my first join

Not quite managing my first join

From
"Rodolfo J. Paiz"
Date:
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@simpaticus.com>


Re: Not quite managing my first join

From
Bob Henkel
Date:


On Apr 9, 2005 3:49 PM, Rodolfo J. Paiz <rpaiz@simpaticus.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@simpaticus.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;

Re: Not quite managing my first join

From
Bob Henkel
Date:


On Apr 9, 2005 4:47 PM, Bob Henkel <luckyratfoot@gmail.com> wrote:


On Apr 9, 2005 3:49 PM, Rodolfo J. Paiz <rpaiz@simpaticus.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@simpaticus.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.

Re: Not quite managing my first join

From
Bob Henkel
Date:


On Apr 9, 2005 9:39 PM, Todd Lewis <lewis-todd@sbcglobal.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@gmail.com
> <mailto:luckyratfoot@gmail.com>> wrote:
>
>
>
>     On Apr 9, 2005 3:49 PM, *Rodolfo J. Paiz* <rpaiz@simpaticus.com
>     <mailto:rpaiz@simpaticus.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@simpaticus.com
>         <mailto:rpaiz@simpaticus.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.