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

This IS a generic SQL query.

DBI is not generic, it is a perl interface to databases.

-alex

On Fri, 22 Jun 2001, Thomas T. Thai wrote:

> 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)
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>