From: Stefan Becker <pgsql@yukonho.de>
To: pgsql-sql@postgresql.org
Subject: Re: regarding join 
Date: Sat, 25 Mar 2006 11:57:35 +0100
User-Agent: KMail/1.6.2
References: <ad39daf90603242236w1f6a54ecx7ba4d1b24dfdc18c@mail.gmail.com>
In-Reply-To: <ad39daf90603242236w1f6a54ecx7ba4d1b24dfdc18c@mail.gmail.com>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Message-Id: <200603251157.35462.pgsql@yukonho.de>

given this.....
	 create table AA (id serial,name varchar(15));
	 create table BB (id serial,name varchar(15));
	insert into AA (name) values ('1243f');
	insert into AA (name) values ('asdfef');
	insert into AA (name) values ('fdbsfd');
	insert into AA (name) values ('btgrt');
	insert into AA (name) values ('crregsewf');
	insert into AA (name) values ('xedrgeef');
	insert into BB (name) values ('243f');
	insert into BB (name) values ('sdfef');
	insert into BB (name) values ('dbsfd');
	insert into BB (name) values ('tgrt');
	insert into BB (name) values ('rregsewf');
	insert into BB (name) values ('edrgeef');

you could try: (if you just need the one column "name")
	 select name from AA union select name from BB order by name;


a real nice way to go about this is:
	 create table CC (id serial,name varchar(15));
	 create table AA() inherits(CC);
	 create table BB() inherits(CC);
	insert into AA (name) values ('1243f');
	insert into AA (name) values ('asdfef');
	insert into AA (name) values ('fdbsfd');
	insert into AA (name) values ('btgrt');
	insert into AA (name) values ('crregsewf');
	insert into AA (name) values ('xedrgeef');
	insert into BB (name) values ('243f');
	insert into BB (name) values ('sdfef');
	insert into BB (name) values ('dbsfd');
	insert into BB (name) values ('tgrt');
	insert into BB (name) values ('rregsewf');
	insert into BB (name) values ('edrgeef');


=3D> select * from AA;
 id |   name
=2D---+-----------
  1 | 1243f
  2 | asdfef
  3 | fdbsfd
  4 | btgrt
  5 | crregsewf
  6 | xedrgeef
(6 rows)

=3D> select * from BB;
 id |   name
=2D---+----------
  7 | 243f
  8 | sdfef
  9 | dbsfd
 10 | tgrt
 11 | rregsewf
 12 | edrgeef
(6 rows)

=3D> select * from CC order by name;
 id |   name
=2D---+-----------
  1 | 1243f
  7 | 243f
  2 | asdfef
  4 | btgrt
  5 | crregsewf
  9 | dbsfd
 12 | edrgeef
  3 | fdbsfd
 11 | rregsewf
  8 | sdfef
 10 | tgrt
  6 | xedrgeef
(12 rows)


by best regards,

Stefan


=2D-=20
email: stefan@net-away.de
tel  : +49 (0)6232-629542=20
       l=C3=A4nger klingeln lassen (Weiterleitung aktiv)
fax  : +49 (0)6232-629544
http://www.net-away.de


Am Samstag, 25. M=C3=A4rz 2006 07:36 schrieb AKHILESH GUPTA:
> hi all,
> below I have created two tables in pgsql with field name as 'name' and 'i=
d'
> as their datatype 'varchar(15)' and 'integer'.
>
> One of the table is:->
> chemical=3D> select * from test1;
>  name  | id
> -------+----
>  akhil |  1
>  b     |  2
>  c     |  3
>  d     |  4
>  e     |  5
>  f     |  6
> (6 rows)
>
> Another table is:->
> chemical=3D> select * from test3;
>  name | id
> ------+----
>  ab   |  1
>  cd   |  2
>  ef   |  3
>  gh   |  4
> (4 rows)
>
> i want the output as:->
> name  | id
> -------+----
>  akhil |  1 -----from test1 table
>  ab   |  1------from test2 table
>  b     |  2-----from test1 table
>  cd   |  2------from test2 table
>  c     |  3-----from test1 table
>  ef   |  3------from test2 table
>  d     |  4-----from test1 table
>  gh   |  4------from test2 table
>  e     |  5-----from test1 table
>  f     |  6-----from test1 table
>
> i have tried all the joins but it makes different fields for different
> tables.
> is there any way out for this kind of output??????????????????
> (plz reply asap)urgent.
>
> THANKS IN ADVANCE
>
> --
> Thanks & Regards,
> Akhilesh
> S/W Trainee (EDP),
> NUCHEM Pvt. Ltd.,
> Faridabad(Haryana)
> GSM:-(+919891606064)
>
> "FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME"