Re: [GENERAL] Unexpected results with joins on dates
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, outer joins 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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unexpected results with joins on dates
On Tue, Jul 12, 2011 at 3:01 PM, David Johnston pol...@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
Re: [GENERAL] Unexpected results with joins on dates
You would have to use a UNION or a Function. Either way, semantically common fields would want to share the same type so they could be output using the same column. If you have additional fields you want to output that are source specific you 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 you need to cast the NULL or if it will use the type found in the second SELECT) and I generally put a source field into the 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-ow...@postgresql.org [mailto:pgsql-general-ow...@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 pol...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unexpected results with joins on dates
Only the first SELECT is used to define column types and names (in the case of NULL AS source2_* I am not positive if you need to cast the NULL or if it will use the type found in the second SELECT) and I generally put a source field into the 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 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
