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