Thread: Not quite managing my first join
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>
On Apr 9, 2005 3:49 PM, Rodolfo J. Paiz <rpaiz@simpaticus.com> wrote:
Well it looks like your outer join is doing exactly like it should.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
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."
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;
On Apr 9, 2005 4:47 PM, Bob Henkel <luckyratfoot@gmail.com> wrote:
Not that this is a big deal, but the has no parent and has no child strings should be switched so it reads correctly.Well it looks like your outer join is doing exactly like it should.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
From the docs at http://www.postgresql.org/docs/8.0/interactive/queries-table-expressions.htmlHere is a simple example that shows your problem and how you can turn those nulls into something
- "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."
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;
On Apr 9, 2005 9:39 PM, Todd Lewis <lewis-todd@sbcglobal.net> wrote:
Try this->http://www.postgresql.org/docs/8.0/interactive/functions-conditional.htmlIs 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.
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.