Обсуждение: regarding join

hi all,
below I have created two tables in pgsql with field name as 'name' and 'id' as their datatype 'varchar(15)' and 'integer'.

One of the table is:->
chemical=> select * from test1;
 name  | id
-------+----
 akhil |  1
 b     |  2
 c     |  3
 d     |  4
 e     |  5
 f     |  6
(6 rows)

Another table is:->
chemical=> 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"

AKHILESH GUPTA <akhilesh.davim@gmail.com> schrieb:

> hi all,
> below I have created two tables in pgsql with field name as 'name' and 'id' as
> their datatype 'varchar(15)' and 'integer'.


> i want the output as:->

... a UNION of this 2 tables:

test=# select * from test1 union select * from test3 order by 2,1;name  | id
-------+----ab    |  1akhil |  1b     |  2cd    |  2c     |  3ef    |  3d     |  4gh    |  4e     |  5f     |  6
(10 rows)


HTH, Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

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
intoBB (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);
createtable BB() inherits(CC);insert into AA (name) values ('1243f');insert into AA (name) values ('asdfef');insert
intoAA (name) values ('fdbsfd');insert into AA (name) values ('btgrt');insert into AA (name) values
('crregsewf');insertinto 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'); 


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

=> select * from BB;id |   name
----+---------- 7 | 243f 8 | sdfef 9 | dbsfd10 | tgrt11 | rregsewf12 | edrgeef
(6 rows)

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


by best regards,

Stefan


--
email: stefan@net-away.de
tel  : +49 (0)6232-629542       länger klingeln lassen (Weiterleitung aktiv)
fax  : +49 (0)6232-629544
http://www.net-away.de







Am Samstag, 25. März 2006 07:36 schrieb AKHILESH GUPTA:
> hi all,
> below I have created two tables in pgsql with field name as 'name' and 'id'
> as their datatype 'varchar(15)' and 'integer'.
>
> One of the table is:->
> chemical=> select * from test1;
>  name  | id
> -------+----
>  akhil |  1
>  b     |  2
>  c     |  3
>  d     |  4
>  e     |  5
>  f     |  6
> (6 rows)
>
> Another table is:->
> chemical=> 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"