Re: select to combine 2 tables

On Fri, 22 Jun 2001 wsheldah(at)lexmark(dot)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(dot)com(at)interlock(dot)lexmark(dot)com> on 06/22/2001
> 03:48:49 PM
>
> To: PostgreSQL General <pgsql-general%postgresql(dot)org(at)interlock(dot)lexmark(dot)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(at)postgresql(dot)org)
>
>
>
>
>
> ---------------------------(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)
>