From: A. Kretschmer <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: right join problem
Date: Fri, 2 Oct 2009 07:45:49 +0200
Message-ID: <20091002054548.GB22496@a-kretschmer.de> (raw)
In-Reply-To: <ddcb1c340910012233m41f6161kaac483c04ef4b320@mail.gmail.com>
References: <ddcb1c340910012233m41f6161kaac483c04ef4b320@mail.gmail.com>
In response to Greenhorn :
> Hi,
>
> I'm trying to retrieve all meter_id from table meter, and for meter_id
> without amount, I'd like it to show nothing, 'null'. I've used right
> join here but it's not giving me my desired result. Is there another
> way to do this? Here's the query I'm trying.
>
> Thanks in advance!
>
> select m.meter_id, mp.meter_id, sum(amount_in_cents) as am
> from meter m
> right join transaction_mpark mp on (m.meter_id = mp.meter_id)
> where date_time between '2009-09-28 00:00:00' and '2009-10-04 23:59:59'
> group by m.meter_id, mp.meter_id
> order by m.meter_id
Use a left join instead?
test=*# select * from master;
id | name
----+----------
1 | master 1
2 | master 2
(2 rows)
test=*# select * from slave;
id | value
----+-------
1 | 1
1 | 2
1 | 3
(3 rows)
test=*# select m.id, s.id, sum(s.value) from master m right join slave s on (m.id=s.id) group by m.id, s.id;
id | id | sum
----+----+-----
1 | 1 | 6
(1 row)
test=*# select m.id, s.id, sum(s.value) from master m left join slave s on (m.id=s.id) group by m.id, s.id;
id | id | sum
----+----+-----
1 | 1 | 6
2 | |
(2 rows)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas.kretschmer@schollglas.com
Subject: Re: right join problem
In-Reply-To: <20091002054548.GB22496@a-kretschmer.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox