Unexpected results with joins on dates

Поиск
Список
Период
Сортировка
От Tim Uckun
Тема Unexpected results with joins on dates
Дата
Msg-id CAGuHJrNPLz+nbQXUf-aHXXjcSht3HPdOkTut=jd7BFh9WixpgA@mail.gmail.com
обсуждение исходный текст
Ответы Re: Unexpected results with joins on dates  (David Johnston <polobo@yahoo.com>)
Re: Unexpected results with joins on dates  (Alban Hertroys <dalroi@solfertje.student.utwente.nl>)
Список 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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Jeff Davis
Дата:
Сообщение: Re: Accidentally truncated pg_type
Следующее
От: David Johnston
Дата:
Сообщение: Re: Unexpected results with joins on dates