Thread: join two tables without a key

On 03/04/2010 11:16, Dino Vliet 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.

How do the rows in the tables relate to each other? You need to decide
first how you match the rows in A and B.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 04/03/2010 11:16 AM, Dino Vliet wrote:
> If I have two tables with the same number of rows but different columns and I want to create one table out of them
whatwould 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
tableshave 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.

You haven't said what you want the result to mean.

- Jeremy

On 03/04/2010 12:32, Dino Vliet wrote:
> --- On Sat, 4/3/10, Raymond O'Donnell <rod@iol.ie> wrote:
> On 03/04/2010 11:16, Dino Vliet 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.
>
> How do the rows in the tables relate to each other? You need to decide
> first how you match the rows in A and B.

> they don' t. It' s pure randomly generated data.

In that case, how about getting the cartesian product of the two tables,
and then LIMITing the result to N rows? - Something like this:

  select
    a.x, a.y, a.z,
    b.p, b.q, b.r
  from
    a, b
  limit N;

....substituting your value of N. It'll be slow if there are a lot of
rows in A and B, mind.

Ray.

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°

Dino,

Not trying to be a "database purist" here, but...

If table A has no key, then why X,Y, and Z belong in one table?
And, table B has no key, then why P,Q, and R belong in one table?

And even more so, why are you trying to put X,Y,Z,P,Q,R into one table?
May be, if you tell us, what business entity/rule you are trying to
implement here, it'll be easier to help you.

Igor Neyman

> -----Original Message-----
> From: Dino Vliet [mailto:dino_vliet@yahoo.com]
> Sent: Saturday, April 03, 2010 7:32 AM
> To: rod@iol.ie
> Cc: pgsql-general@postgresql.org
> Subject: Re: join two tables without a key
>
> --- On Sat, 4/3/10, Raymond O'Donnell <rod@iol.ie> wrote:
>
>
>
>     From: Raymond O'Donnell <rod@iol.ie>
>     Subject: Re: [GENERAL] join two tables without a key
>     To: "Dino Vliet" <dino_vliet@yahoo.com>
>     Cc: pgsql-general@postgresql.org
>     Date: Saturday, April 3, 2010, 1:01 PM
>
>
>     On 03/04/2010 11:16, Dino Vliet 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.
>
>     How do the rows in the tables relate to each other? You
> need to decide
>     first how you match the rows in A and B.
>
>     Ray.
>
>
>     --
>     Raymond O'Donnell :: Galway :: Ireland
>     rod@iol.ie
>
>
>
> Hi Ray,
>
>
>
>
>
>
>
> they don' t. It' s pure randomly generated data.
>
>
>
>
> Brgds
>
>
>
>
>
>