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.
