I'm working for an energy meter company. The energy meters reports
consumption to a MySQL database. We are now in the works of developing
virtual meters in wich our customers can create formulas of real
meters.

Every energy meter has it's own table with three columns; consumption,
meter_reading and date_time.

SELECT (COALESCE(t2.consumption, 0)+COALESCE(t1.consumption, 0)) AS
cons, t2.date_time, t1.date_time FROM t2 LEFT JOIN t1 ON
t1.date_time = t2.date_time WHERE (t2.date_time BETWEEN '2010-09-01
00:00:00' AND '2010-09-30 23:59:59') OR (t1.date_time BETWEEN
'2010-09-01 00:00:00' AND '2010-09-30 23:59:59')

if there are data in t1 wich is not in t2, the query will not match
it, how can I JOIN both (or more) ways?

Best regards Johnny

Il 01/10/2010 14.30, Johnny Beeper ha scritto:

If you have a left join and add some where condition on the t2 table...
the query won't properly work.
You can try with a sub-query like

SELECT ... FROM t1 left join
(
select ... from t2 where ...
) s on t1.date_time=s.date_time
where t1 ...

M.

On 1 Okt, 16:08, Mau C <nob... (AT) hotmail (DOT) com> wrote:
The problem is not with the WHERE clause, even if I remove it, it
won't fetch all rows.

Il 01/10/2010 16.24, Johnny Beeper ha scritto:
Dumb question: are you sure t1 contains rows that follow the where
condition ?

M.

On 1 Okt, 16:26, Mau C <nob... (AT) hotmail (DOT) com> wrote:
Let me explain with an example so it might me easier to understand.

t1 has the rows

consumption date_time
1 2010-08-01 00:00:00
2 2010-08-02 00:00:00
3 2010-08-03 00:00:00
4 2010-08-04 00:00:00

t2 has these rows
consumption date_time
1 2010-08-03 00:00:00
2 2010-08-04 00:00:00
3 2010-08-05 00:00:00
4 2010-08-06 00:00:00

In this exaple I want it to summerize the rows where date_time matches
both ways or the one that exits in either table.

So the resulting set should be.

consumption date_time
1 2010-08-01 00:00:00 (only t1)
2 2010-08-02 00:00:00 (only t1)
4 2010-08-03 00:00:00 (3 from t1 + 1 from t2)
6 2010-08-04 00:00:00 (4 from t1 + 2 from t2)
3 2010-08-05 00:00:00 (only t2)
4 2010-08-06 00:00:00 (only t2)

On 1 Oct, 13:30, Johnny Beeper <superkon... (AT) gmail (DOT) com> wrote:
If you write programs like you wrote that query, I suggest that you
give up now and get a job more suited to someone who can't be bothered
to structure things properly.

If you lay it out neatly, I'll take a look at it for you.

In article <9e93883e-2397-4fb1-a9aa-ccb3a18ae456 (AT) g18g2000yqk (DOT) googlegroups.com>,
Johnny Beeper <superkon.ee (AT) gmail (DOT) com> wrote:
It doesn't look like MySQL has a bi-directional outer join, so I think
you need to use UNION of a LEFT JOIN and a RIGHT JOIN:

SELECT
(COALESCE(t2.consumption, 0)+COALESCE(t1.consumption, 0)) AS cons,
t2.date_time, t1.date_time
FROM t2 LEFT JOIN t1 ON t1.date_time = t2.date_time
WHERE t2.date_time >= '2010-09-01 00:00:00'
AND t2.date_time < '2010-10-01 00:00:00'
UNION
SELECT
(COALESCE(t2.consumption, 0)+COALESCE(t1.consumption, 0)) AS cons,
t2.date_time, t1.date_time
FROM t2 RIGHT JOIN t1 ON t1.date_time = t2.date_time
WHERE t1.date_time >= '2010-09-01 00:00:00'
AND t1.date_time < '2010-10-01 00:00:00'

Things to notice:

1. Since the join is on equality of date_time, you only need the
WHERE condition to be on the driving table (t2 for the left join,
t1 for the right join).

2. By using UNION and *not* UNION ALL, it will eliminate the rows
that are duplicated between both halves due to being in both tables.

3. Your BETWEEN conditions would have excluded 2010-09-30 23:59:59.xxx
where xxx is non-zero. Better to use >= at the bottom end, and then <
at the top end, comparing to the beginning of the next month. This
also means you don't need to know how many days in the month!

Hope this helps!

Tony

--
Tony Mountifield
Work: tony (AT) softins (DOT) co.uk - http://www.softins.co.uk
Play: tony (AT) mountifield (DOT) org - http://tony.mountifield.org