Unexpected results with joins on dates

From: Tim Uckun <timuckun(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Unexpected results with joins on dates
Date: 2011-07-12 02:53:39
Message-ID: CAGuHJrNPLz+nbQXUf-aHXXjcSht3HPdOkTut=jd7BFh9WixpgA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have three tables. traffic, sales and dates. Both the traffic table
and the sales table has multiple entries per date with each row
representing the date, some subdivision, and the total. For example
every day five divisions could be reporting their sales so there would
be five entries in the sales table for that date.

The dates table just has one field and it just has a date in it
(unique). I set that up for testing purposes.

I have the following query which I am trying to make sense of.

select
(select count(id) from sales) as sales_count,
(select count(id) from traffic) as traffic_count,
(select count(traffic.date) from traffic inner join sales on
traffic.date = sales.date) as two_table_join_count,
(select count(dates.date) from dates
inner join traffic on dates.date = traffic.date
inner join sales on sales.date = dates.date) as
three_table_join_count;

running this query gives me this result

169157; 49833 ;25121853; 25121853

On the third select (two table join) it doesn't matter if I change it
to a right join, full join left outer join I get the same number so it
looks like it's doing a cross join no matter what. It also doesn't
matter if I do a select count(*)

Could somebody explain what is happening here?

Thanks.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2011-07-12 03:01:28 Re: Unexpected results with joins on dates
Previous Message Jeff Davis 2011-07-12 02:12:46 Re: Accidentally truncated pg_type