select to combine 2 tables

Started by Thomas T. Thaialmost 25 years ago10 messagesgeneral

tom@minnesota.com

almost 25 years ago

i have two tables:

select * from cat_cat;
+--------+------+--------------+
| rec_id | path | name |
+--------+------+--------------+
| 1 | 0202 | water crafts |
| 2 | 02 | classifieds |
| 3 | 0204 | real estate |
| 4 | 0201 | auto |
| 5 | 0203 | pets |
+--------+------+--------------+

select * from cat_alias;
+--------+------+------+--------+
| rec_id | path | link | name |
+--------+------+------+--------+
| 1 | 02@@ | 0201 | cars |
| 2 | 02@@ | | myLink |
+--------+------+------+--------+

i would like to have a query so that it combines two tables stacked on top
of each other instead of side by side:

*** totally incorrect query***
SELECT * FROM cat_cat as cc, cat_alias as ca WHERE path like '02%';

so that i'd get this:

+--------+------+------+--------------+
| rec_id | path | link | name |
+--------+------+------+--------------+
| 1 | 0202 | | water crafts |
| 2 | 02 | | classifieds |
| 3 | 0204 | | real estate |
| 4 | 0201 | | auto |
| 5 | 0203 | | pets |
| 1 | 02@@ | 0201 | cars |
| 2 | 02@@ | | myLink |
+--------+------+------+--------------+

what's the correct query to accomplish that task?

i could stuff everything in one table to begin with like so:

CREATE TABLE cat_alias (
rec_id int(11) NOT NULL PRIMARY KEY,
path char(256) NOT NULL,
link char(256) NOT NULL,
name char(64) NOT NULL
);

but since the 'link' column is an alias (symbolic link) pointing to a real
path and is not used often, it would be waste of space.

Noname

wsheldah@lexmark.com

almost 25 years ago

In reply to: Thomas T. Thai (#1)

Re: select to combine 2 tables

Use a union query:

select rec_id, path, '' as link, name from cat_cat
UNION
select rec_id, path, link, name from cat_alias

Notice that the two select statements need to have the same number of columns,
and the fields should be in the same order. Field names don't have to match as
long as the datatypes are compatible.

"Thomas T. Thai" <tom%minnesota.com@interlock.lexmark.com> on 06/22/2001
03:48:49 PM

To: PostgreSQL General <pgsql-general%postgresql.org@interlock.lexmark.com>
cc: (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject: [GENERAL] select to combine 2 tables

i have two tables:

i would like to have a query so that it combines two tables stacked on top
of each other instead of side by side:

*** totally incorrect query***
SELECT * FROM cat_cat as cc, cat_alias as ca WHERE path like '02%';

so that i'd get this:

what's the correct query to accomplish that task?

i could stuff everything in one table to begin with like so:

CREATE TABLE cat_alias (
rec_id int(11) NOT NULL PRIMARY KEY,
path char(256) NOT NULL,
link char(256) NOT NULL,
name char(64) NOT NULL
);

but since the 'link' column is an alias (symbolic link) pointing to a real
path and is not used often, it would be waste of space.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Import Notes

Resolved by subject fallback

Thomas T. Thai

tom@minnesota.com

almost 25 years ago

In reply to: Noname (#2)

Re: select to combine 2 tables

On Fri, 22 Jun 2001 wsheldah@lexmark.com wrote:

Use a union query:

select rec_id, path, '' as link, name from cat_cat
UNION
select rec_id, path, link, name from cat_alias

there is no way to do this in a generic DBI way? i need for this to work
across diff kind of DBs.

Show quoted text

Notice that the two select statements need to have the same number of columns,
and the fields should be in the same order. Field names don't have to match as
long as the datatypes are compatible.

"Thomas T. Thai" <tom%minnesota.com@interlock.lexmark.com> on 06/22/2001
03:48:49 PM

To: PostgreSQL General <pgsql-general%postgresql.org@interlock.lexmark.com>
cc: (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject: [GENERAL] select to combine 2 tables

i have two tables:

select * from cat_cat;
+--------+------+--------------+
| rec_id | path | name |
+--------+------+--------------+
| 1 | 0202 | water crafts |
| 2 | 02 | classifieds |
| 3 | 0204 | real estate |
| 4 | 0201 | auto |
| 5 | 0203 | pets |
+--------+------+--------------+

select * from cat_alias;
+--------+------+------+--------+
| rec_id | path | link | name |
+--------+------+------+--------+
| 1 | 02@@ | 0201 | cars |
| 2 | 02@@ | | myLink |
+--------+------+------+--------+

i would like to have a query so that it combines two tables stacked on top
of each other instead of side by side:

*** totally incorrect query***
SELECT * FROM cat_cat as cc, cat_alias as ca WHERE path like '02%';

so that i'd get this:

+--------+------+------+--------------+
| rec_id | path | link | name |
+--------+------+------+--------------+
| 1 | 0202 | | water crafts |
| 2 | 02 | | classifieds |
| 3 | 0204 | | real estate |
| 4 | 0201 | | auto |
| 5 | 0203 | | pets |
| 1 | 02@@ | 0201 | cars |
| 2 | 02@@ | | myLink |
+--------+------+------+--------------+

what's the correct query to accomplish that task?

i could stuff everything in one table to begin with like so:

CREATE TABLE cat_alias (
rec_id int(11) NOT NULL PRIMARY KEY,
path char(256) NOT NULL,
link char(256) NOT NULL,
name char(64) NOT NULL
);

but since the 'link' column is an alias (symbolic link) pointing to a real
path and is not used often, it would be waste of space.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Alex Pilosov

alex@pilosoft.com

almost 25 years ago

In reply to: Thomas T. Thai (#3)

Re: Re: select to combine 2 tables

This IS a generic SQL query.

DBI is not generic, it is a perl interface to databases.

-alex

On Fri, 22 Jun 2001, Thomas T. Thai wrote:

Show quoted text

On Fri, 22 Jun 2001 wsheldah@lexmark.com wrote:

Use a union query:

select rec_id, path, '' as link, name from cat_cat
UNION
select rec_id, path, link, name from cat_alias

there is no way to do this in a generic DBI way? i need for this to work
across diff kind of DBs.

Notice that the two select statements need to have the same number of columns,
and the fields should be in the same order. Field names don't have to match as
long as the datatypes are compatible.

"Thomas T. Thai" <tom%minnesota.com@interlock.lexmark.com> on 06/22/2001
03:48:49 PM

To: PostgreSQL General <pgsql-general%postgresql.org@interlock.lexmark.com>
cc: (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject: [GENERAL] select to combine 2 tables

i have two tables:

select * from cat_cat;
+--------+------+--------------+
| rec_id | path | name |
+--------+------+--------------+
| 1 | 0202 | water crafts |
| 2 | 02 | classifieds |
| 3 | 0204 | real estate |
| 4 | 0201 | auto |
| 5 | 0203 | pets |
+--------+------+--------------+

select * from cat_alias;
+--------+------+------+--------+
| rec_id | path | link | name |
+--------+------+------+--------+
| 1 | 02@@ | 0201 | cars |
| 2 | 02@@ | | myLink |
+--------+------+------+--------+

i would like to have a query so that it combines two tables stacked on top
of each other instead of side by side:

*** totally incorrect query***
SELECT * FROM cat_cat as cc, cat_alias as ca WHERE path like '02%';

so that i'd get this:

+--------+------+------+--------------+
| rec_id | path | link | name |
+--------+------+------+--------------+
| 1 | 0202 | | water crafts |
| 2 | 02 | | classifieds |
| 3 | 0204 | | real estate |
| 4 | 0201 | | auto |
| 5 | 0203 | | pets |
| 1 | 02@@ | 0201 | cars |
| 2 | 02@@ | | myLink |
+--------+------+------+--------------+

what's the correct query to accomplish that task?

i could stuff everything in one table to begin with like so:

CREATE TABLE cat_alias (
rec_id int(11) NOT NULL PRIMARY KEY,
path char(256) NOT NULL,
link char(256) NOT NULL,
name char(64) NOT NULL
);

but since the 'link' column is an alias (symbolic link) pointing to a real
path and is not used often, it would be waste of space.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Tom Lane

tgl@sss.pgh.pa.us

almost 25 years ago

In reply to: Thomas T. Thai (#3)

Re: Re: select to combine 2 tables

"Thomas T. Thai" <tom@minnesota.com> writes:

Use a union query:

select rec_id, path, '' as link, name from cat_cat
UNION
select rec_id, path, link, name from cat_alias

there is no way to do this in a generic DBI way? i need for this to work
across diff kind of DBs.

Huh? That *is* the generic, fully-SQL-standard way.

regards, tom lane

Charles Tassell

ctassell@isn.net

almost 25 years ago

In reply to: Thomas T. Thai (#1)

Re: select to combine 2 tables

I think you can do what you want with the UNION command. I haven't tested
this with your schema, but something like

SELECT rec_id, path, name, NULL as link FROM cat_cat
UNION
SELECT rec_id, path, name, link FROM cat_alias;

Should do it. You probably want to read the full docs on the UNION clause
in the SELECT page of the docs to see all the implications.

At 04:48 PM 6/22/01, Thomas T. Thai wrote:

Show quoted text

i have two tables:

select * from cat_cat;
+--------+------+--------------+
| rec_id | path | name |
+--------+------+--------------+
| 1 | 0202 | water crafts |
| 2 | 02 | classifieds |
| 3 | 0204 | real estate |
| 4 | 0201 | auto |
| 5 | 0203 | pets |
+--------+------+--------------+

select * from cat_alias;
+--------+------+------+--------+
| rec_id | path | link | name |
+--------+------+------+--------+
| 1 | 02@@ | 0201 | cars |
| 2 | 02@@ | | myLink |
+--------+------+------+--------+

i would like to have a query so that it combines two tables stacked on top
of each other instead of side by side:

*** totally incorrect query***
SELECT * FROM cat_cat as cc, cat_alias as ca WHERE path like '02%';

so that i'd get this:

+--------+------+------+--------------+
| rec_id | path | link | name |
+--------+------+------+--------------+
| 1 | 0202 | | water crafts |
| 2 | 02 | | classifieds |
| 3 | 0204 | | real estate |
| 4 | 0201 | | auto |
| 5 | 0203 | | pets |
| 1 | 02@@ | 0201 | cars |
| 2 | 02@@ | | myLink |
+--------+------+------+--------------+

what's the correct query to accomplish that task?

i could stuff everything in one table to begin with like so:

CREATE TABLE cat_alias (
rec_id int(11) NOT NULL PRIMARY KEY,
path char(256) NOT NULL,
link char(256) NOT NULL,
name char(64) NOT NULL
);

but since the 'link' column is an alias (symbolic link) pointing to a real
path and is not used often, it would be waste of space.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Thomas T. Thai

tom@minnesota.com

almost 25 years ago

In reply to: Tom Lane (#5)

Re: Re: select to combine 2 tables

On Fri, 22 Jun 2001, Tom Lane wrote:

"Thomas T. Thai" <tom@minnesota.com> writes:

Use a union query:

select rec_id, path, '' as link, name from cat_cat
UNION
select rec_id, path, link, name from cat_alias

there is no way to do this in a generic DBI way? i need for this to work
across diff kind of DBs.

Huh? That *is* the generic, fully-SQL-standard way.

the project i'm doing this for is opensource and i need it to work in both
postgresql and mysql. the above statement wouldn't work in mysql.

Trond Eivind Glomsrød

teg@redhat.com

almost 25 years ago

In reply to: Thomas T. Thai (#7)

Re: Re: select to combine 2 tables

"Thomas T. Thai" <tom@minnesota.com> writes:

On Fri, 22 Jun 2001, Tom Lane wrote:

"Thomas T. Thai" <tom@minnesota.com> writes:

Use a union query:

select rec_id, path, '' as link, name from cat_cat
UNION
select rec_id, path, link, name from cat_alias

there is no way to do this in a generic DBI way? i need for this to work
across diff kind of DBs.

Huh? That *is* the generic, fully-SQL-standard way.

the project i'm doing this for is opensource and i need it to work in both
postgresql and mysql. the above statement wouldn't work in mysql.

MySQL isn't close to SQL compatible.

--
Trond Eivind Glomsrï¿½d
Red Hat, Inc.

Alex Pilosov

alex@pilosoft.com

almost 25 years ago

In reply to: Thomas T. Thai (#7)

Re: Re: select to combine 2 tables

On Fri, 22 Jun 2001, Thomas T. Thai wrote:

Huh? That *is* the generic, fully-SQL-standard way.

the project i'm doing this for is opensource and i need it to work in both
postgresql and mysql. the above statement wouldn't work in mysql.

MySQL is not a standards conforming database. You must not ask on
postgresql list for a workaround against mysql brain-damage.

Alternatively, the answer to your question (assuming you must get it to
work on mysql and assuming that mysql does not understand the 'union'), is
to do two separate queries on these tables and join them together in your
perl code. But you knew that, right?

-alex

#10

Thomas T. Thai

tom@minnesota.com

almost 25 years ago

In reply to: Alex Pilosov (#9)

Re: Re: select to combine 2 tables

On Fri, 22 Jun 2001, Alex Pilosov wrote:

On Fri, 22 Jun 2001, Thomas T. Thai wrote:

Huh? That *is* the generic, fully-SQL-standard way.

the project i'm doing this for is opensource and i need it to work in both
postgresql and mysql. the above statement wouldn't work in mysql.

MySQL is not a standards conforming database. You must not ask on
postgresql list for a workaround against mysql brain-damage.

Alternatively, the answer to your question (assuming you must get it to
work on mysql and assuming that mysql does not understand the 'union'), is
to do two separate queries on these tables and join them together in your
perl code. But you knew that, right?

yes, but i was looking at the one query thing. i'm cheap! :)