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

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)