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

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)