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