very simple: How can I multiply tables?

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

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
**********************************************************************

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

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.

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.