Thread: Unexpected results with joins on dates
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.
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 insteadof just counts. David J. On Jul 11, 2011, at 22:53, Tim Uckun <timuckun@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
On 12 Jul 2011, at 4:53, Tim Uckun wrote: (Edited to take the irrelevant stuff out) > select count(traffic.date) from traffic inner join sales on traffic.date = sales.date > running this query gives me this result > 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? Apparently you don't have any records in traffic where there's no corresponding date in sales or vice versa. Hence, outerjoins give the same result as inner joins. If that's not the case then we're going to need more details, such as the definitions of the tables. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4e1c140112091081743685!
On Tue, Jul 12, 2011 at 3:01 PM, David Johnston <polobo@yahoo.com> wrote: > If traffic has 5 records on a date and sales has 4 on the same date you would output 20 records for that date. What would I have to do in order to get 9 records instead of 20. Like a union but with dissimilar schema.
You would have to use a UNION or a Function. Either way, semantically common fields would want to share the same type sothey could be output using the same column. If you have additional fields you want to output that are source specificyou can do so and just output NULL from invalid sources. SELECT 'SOURCE1' AS source, common1, common2, common3, source1_1, source1_2, NULL AS source2_1, NULL AS source2_2 FROM source1 UNION SELECT 'SOURCE2' AS source, common1, common2, common3, NULL, NULL, source2_1, source2_2 FROM source 2 Only the first SELECT is used to define column types and names (in the case of NULL AS source2_* I am not positive if youneed to cast the NULL or if it will use the type found in the second SELECT) and I generally put a "source" field intothe output with a textual representation of which table the record originated from. Sample result data: SOURCE1,C1,c2,c3,s11,c12,null,null SOURCE2,C1,c2,c3,null,null,c21,c22 David J. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tim Uckun Sent: Tuesday, July 12, 2011 6:13 AM To: David Johnston Cc: pgsql-general Subject: Re: [GENERAL] Unexpected results with joins on dates On Tue, Jul 12, 2011 at 3:01 PM, David Johnston <polobo@yahoo.com> wrote: > If traffic has 5 records on a date and sales has 4 on the same date you would output 20 records for that date. What would I have to do in order to get 9 records instead of 20. Like a union but with dissimilar schema. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
> Only the first SELECT is used to define column types and names (in the case of NULL AS source2_* I am not positive if youneed to cast the NULL or if it will use the type found in the second SELECT) and I generally put a "source" field intothe output with a textual representation of which table the record originated from. > Seems like it would be more efficient just to create a table (temp or otherwise) with and pump data into that. Anyway thanks for the help.