Thread: 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.
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:
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)
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) >
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: > 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 > >
"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
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:
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:
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)
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.
"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.
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
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! :)