Re: Not quite managing my first join - Mailing list pgsql-novice
| From | Bob Henkel |
|---|---|
| Subject | Re: Not quite managing my first join |
| Date | |
| Msg-id | 762e5c050409144718be350c@mail.gmail.com Whole thread Raw |
| In response to | Not quite managing my first join ("Rodolfo J. Paiz" <rpaiz@simpaticus.com>) |
| Responses |
Re: Not quite managing my first join
|
| List | pgsql-novice |
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;
pgsql-novice by date: