Re: Re: select to combine 2 tables

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(at)lexmark(dot)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(dot)com(at)interlock(dot)lexmark(dot)com> on 06/22/2001
> > 03:48:49 PM
> >
> > To: PostgreSQL General <pgsql-general%postgresql(dot)org(at)interlock(dot)lexmark(dot)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(at)postgresql(dot)org)
> >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>