Re: select to combine 2 tables - Mailing list pgsql-general

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