select to combine 2 tables

From: "Thomas T(dot) Thai" <tom(at)minnesota(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: select to combine 2 tables
Date: 2001-06-22 19:48:49
Message-ID: Pine.NEB.4.21.0106221439330.24588-100000@ns01.minnesota.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Browse pgsql-general by date

  From Date Subject
Next Message Peter Eisentraut 2001-06-22 20:03:47 Re: TCP/IP Sockets, UNIX Sockets
Previous Message Daniel Åkerud 2001-06-22 19:32:05 Re: web site suggestion