Not quite managing my first join

Поиск
Список
Период
Сортировка
От Rodolfo J. Paiz
Тема Not quite managing my first join
Дата
Msg-id 1113079753.8752.25.camel@rodolfo.casa.paiz.org
обсуждение исходный текст
Ответы Re: Not quite managing my first join  (Bob Henkel <luckyratfoot@gmail.com>)
Список pgsql-novice
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>


В списке pgsql-novice по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Trigger won't execute when using sp with input parameters
Следующее
От: Bob Henkel
Дата:
Сообщение: Re: Not quite managing my first join