Thread: very simple: How can I multiply tables?

very simple: How can I multiply tables?

From
"Mikhail V. Majorov"
Date:
Hi, question

How can I multiply tables using SELECT?

Table 1

id     surname
--------------
1    AAA
2    BBB
3    CCC
4    DDD

Table 2

dt
---
12
35

RESULT

id    surname        dt
---------------------------
1    AAA        12
2    BBB        12
3    CCC        12
4    DDD        12
1    AAA        35
2    BBB        35
3    CCC        35
4    DDD        35

Re: very simple: How can I multiply tables?

From
Heiko Irrgang
Date:
On Wed, Feb 14, 2001 at 05:49:01PM +0300, Mikhail V. Majorov wrote:
> Hi, question
>
> How can I multiply tables using SELECT?
>
> Table 1
>
> id     surname
> --------------
> 1    AAA
>
> Table 2
>
> dt
> ---
> 12
>
> RESULT
>
> id    surname        dt
> ---------------------------
> 1    AAA        12

I think what you want is the following:
table1:
id surename
===========
1  AAA

table2:
id dt
=====
1  12

select table1.id as id, table1.surname as surname, table2.dt as dt from table1, table2 where table1.id = table2.id;

--
SC-Networks                             www: www.SC-Networks.de
Web Design, Netzwerke,
3D Animation und Multimedia
Heiko Irrgang                           Tel.: 08856/9392-00
Im Thal 2                               Fax:  08856/9392-01

82377 Penzberg                          Mail: Irrgang@SC-Networks.de

RE: very simple: How can I multiply tables?

From
Michael Ansley
Date:

INSERT INTO table3 (id, surname, dt) SELECT table1.id, table1.surname, table2.dt FROM table1, table2;
or
CREATE TABLE table3 AS SELECT table1.id, table1.surname, table2.dt FROM table1, table2;

It's called a cross-join.

Cheers...

MikeA

-----Original Message-----
From: Mikhail V. Majorov [mailto:mik@ttn.ru]
Sent: 14 February 2001 14:49
To: pgsql-general@postgresql.org
Subject: [GENERAL] very simple: How can I multiply tables?

Hi, question

How can I multiply tables using SELECT?

Table 1

id      surname
--------------
1       AAA
2       BBB
3       CCC
4       DDD

Table 2

dt
---
12
35

RESULT

id      surname         dt
---------------------------
1       AAA             12
2       BBB             12
3       CCC             12
4       DDD             12
1       AAA             35
2       BBB             35
3       CCC             35
4       DDD             35

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************

Re: very simple: How can I multiply tables?

From
Holger Klawitter
Date:
Yes, very simple!

> How can I multiply tables using SELECT?

select * from tbl1, tbl2;

With kind regards / Mit freundlichem Gruß
    Holger Klawitter
--
Holger Klawitter
holger@klawitter.de                             http://www.klawitter.de


Re: Re: very simple: How can I multiply tables?

From
"Mikhail V. Majorov"
Date:
Holger Klawitter wrote:
>
> Yes, very simple!
>
> > How can I multiply tables using SELECT?
>
> select * from tbl1, tbl2;

I think so. But in real I have some problem with empty table.
Please, look at my example.

ttn=# create table t3 (id int4, surname text);
CREATE
ttn=# create table t2 (dt int4);
CREATE
ttn=# insert into t2 values ('4');
INSERT 34461 1
ttn=# insert into t2 values ('56');
INSERT 34462 1
ttn=# select * from t2,t3;
 dt | id | surname
----+----+---------
(0 rows)

ttn=#

As I know theory I must see this:

 dt | id | surname
----+----+---------
4    null    null
56    null    null

Result is change when I insert data in t3.

ttn=# insert into t3 values ('100', 'test');
INSERT 34463 1
ttn=# insert into t3 values ('101', 'test2');
INSERT 34464 1
ttn=# select * from t2,t3;
 dt | id  | surname
----+-----+---------
  4 | 100 | test
 56 | 100 | test
  4 | 101 | test2
 56 | 101 | test2
(4 rows)

ttn=#

Is it bug or normal?

Best regards,
Mik.

Re: very simple: How can I multiply tables?

From
"Mikhail V. Majorov"
Date:
> Michael Ansley wrote:
>
> INSERT INTO table3 (id, surname, dt) SELECT table1.id, table1.surname,
> table2.dt FROM table1, table2;
> or
> CREATE TABLE table3 AS SELECT table1.id, table1.surname, table2.dt
> FROM table1, table2;
>
> It's called a cross-join.

You are absolutely right.
But I don't find any information about JOIN in SELECT sentences. :(
Could you give me example of LEFT or RIGHT JOIN.

Best regards,
Mik.