Re: Unexpected results with joins on dates

If traffic has 5 records on a date and sales has 4 on the same date you would output 20 records for that date.

Instead of dealing with the entire table just pick out a couple of dates and show the results of the join in detail instead of just counts.

David J.

On Jul 11, 2011, at 22:53, Tim Uckun <timuckun(at)gmail(dot)com> wrote:

> 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.
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general