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:
>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(at)postgresql(dot)org)