Re: 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-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)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(at)yahoo(dot)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(at)postgresql(dot)org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general