X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id BA3069FA357
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon,  3 Jul 2006 12:54:21 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 15835-08 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon,  3 Jul 2006 12:54:16 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from fil.office.ngn.de (port-83-236-131-42.static.qsc.de
	[83.236.131.42])
	by postgresql.org (Postfix) with ESMTP id 626F59FA373
	for <pgsql-sql@postgresql.org>; Mon,  3 Jul 2006 12:54:16 -0300 (ADT)
Received: from [192.168.9.222] (taifun.becks.net [192.168.9.222])
	by fil.office.ngn.de (8.13.7/8.13.7) with ESMTP id k63Fs8o4013285;
	Mon, 3 Jul 2006 17:54:14 +0200
Message-ID: <44A93DA0.7080201@laokoon.IN-Berlin.DE>
Date: Mon, 03 Jul 2006 17:54:08 +0200
From: Thomas Beutin <psql@laokoon.IN-Berlin.DE>
Organization: (private)
User-Agent: Thunderbird 1.5.0.4 (X11/20060607)
MIME-Version: 1.0
To: filippo <filippo2991@virgilio.it>
CC: pgsql-sql@postgresql.org
Subject: Re: join two tables with sharing some columns between two
References: <1151329151.231866.175920@u72g2000cwu.googlegroups.com>
In-Reply-To: <1151329151.231866.175920@u72g2000cwu.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200607/32
X-Sequence-Number: 25606

Hi,

filippo wrote:
> I have two tables like these: (this is an example, the actual tables
> have diffferent fields and meanings)
> 
> TABLE1
> id
> person_name
> date_arrival
> date_departure
> 
> TABLE2
> id
> car_description
> date_arrival
> date_departure
> 
> I'd like to make a query to have such resulting table
> 
> RESULTING_TABLE
> id
> person_name
> car_description
> date_arrival
> date_departure
> 
> the id is the primary key for the three tables and it's unique for the
> three (id in table1 cannot be in table2, I use the same counter to
> generate the id for table1 and table2).

SELECT
 id, person_name, NULL AS car_description, date_arrival, date_departure
 FROM TABLE1
UNION
SELECT
 id, NULL AS person_name, car_description, date_arrival, date_departure
 FROM TABLE2
ORDER BY <what you want>


But be careful, w/o constraints there's no guarantee that the id's are
really unique on both tables!

HTH,
-tb