Re: join two tables without a key - Mailing list pgsql-general

From Andreas Kretschmer
Subject Re: join two tables without a key
Date
Msg-id 20100403120756.GA23274@tux
Whole thread Raw
In response to join two tables without a key  (Dino Vliet <dino_vliet@yahoo.com>)
List pgsql-general
Dino Vliet <dino_vliet@yahoo.com> wrote:

> Hi postgresql list,
>
>
> If I have two tables with the same number of rows but different columns and I
> want to create one table out of them what would be the way to do that in
> postgresql?
>
>
>
>
> Table A has N number of rows and columns X,Y,Z and Table B has N number of rows
> and P,Q,R as columns. None of the tables have a column which can be used as a
> key.
>
> The resulting table should have N number of rows and X,Y,Z,P,Q,R as columns.

Stupid table design, but okay:


test=# select * from a;
 a1  | a2  | a3
-----+-----+-----
 100 | 101 | 102
 103 | 104 | 105
 106 | 107 | 108
 109 | 110 | 111
(4 Zeilen)

Zeit: 0,378 ms
test=*# select * from b;
 b1  | b2  | b3
-----+-----+-----
 201 | 202 | 203
 204 | 205 | 206
 207 | 208 | 209
 210 | 211 | 212
(4 Zeilen)

Zeit: 0,317 ms
test=*# create sequence sa;
CREATE SEQUENCE
Zeit: 18,618 ms
test=*# create sequence sb;
CREATE SEQUENCE
Zeit: 0,939 ms
test=*# select foo_a.*, foo_b.* from (select nextval('sa') as id_a,*
from a) foo_a left join (select nextval('sb') as id_b,* from b) foo_b on
foo_a.id_A=foo_b.id_b;
 id_a | a1  | a2  | a3  | id_b | b1  | b2  | b3
------+-----+-----+-----+------+-----+-----+-----
    1 | 100 | 101 | 102 |    1 | 201 | 202 | 203
    2 | 103 | 104 | 105 |    2 | 204 | 205 | 206
    3 | 106 | 107 | 108 |    3 | 207 | 208 | 209
    4 | 109 | 110 | 111 |    4 | 210 | 211 | 212
(4 Zeilen)

Zeit: 0,618 ms



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."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

pgsql-general by date:

Previous
From: Raymond O'Donnell
Date:
Subject: Re: join two tables without a key
Next
From: Boszormenyi Zoltan
Date:
Subject: Re: Advice on webbased database reporting