From: | Tim Uckun <tim(at)basediary(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Unexpected results when joining on date fields |
Date: | 2011-07-10 23:58:33 |
Message-ID: | CAGuHJrMYkWt15=Tf7e-dKhqkeEAnzpcPpdTwF9LycF2ycJyJJA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have two tables, traffic and sales. Each one has a date field and
lists the traffic and sales broken down by various parameters
(multiple rows for each date).
If I run select (select count(*) from traffic) as traffic, (select
count(*) from sales) as sales; I get the following 49383;167807
if I run select count(*) from traffic t inner join sales s on t.date
= s.date I get 24836841.
If I change the join to a left join, right join, full join I get the
same number of records.
So I created a data table which just has the dates in it and ran this query.
select count(d.date) from dates d
inner join traffic t on t.date = d.date
inner join sales s on s.date = d.date
And I get the same number 24836841
Same goes for right joins on the above query. Left joins of course
give a different answer as there are more dates in the date table than
there are in the other tables.
I am a bit perplexed by what is happening here.
Cheers
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Nolan | 2011-07-11 04:01:13 | Re: [HACKERS] Creating temp tables inside read only transactions |
Previous Message | Darren Duncan | 2011-07-09 20:07:25 | Re: [HACKERS] Creating temp tables inside read only transactions |