List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 23 2002 5:20pm
Subject:Re: multi-table select (not a join)
View as plain text  
At 13:16 -0400 9/23/02, Jesse Sheidlower wrote:
>On Mon, Sep 23, 2002 at 11:20:49AM -0500, Paul DuBois wrote:
>
>I had a question about the use of the UNION command in this
>context.
>
>The original poster asked about getting the name of the
>_table_ as well as some other data, which would seem to be
>relatively necessary for doing many types of things with the
>results of the query. For example, if you issue a query that
>gives you the union of seven different tables, and then you
>want to do another query based on these results, you'll need
>to know which of the seven tables a particular result came
>from. The docs on UNION don't seem to address this, none of
>the responses mentioned it, and I can't seem to find any
>discussion of how to retrieve the table name in a SELECT query
>(I acknowledge that most of the time you wouldn't need it, but
>in a UNION you might).
>
>How do you get the table name returned as part of the query
>results? Or am I misunderstanding how one would work with the
>results?
>
>Jesse Sheidlower

You can't get the name of the table *from the query*.  However,
I assume that you already know the name of the table, or you wouldn't
be able to write the query in the first place. :-)

So just select an extra column:

CREATE TABLE tmp SELECT 't1' AS tbl_name, t1.* FROM t1 ... ;
INSERT INTO tmp SELECT 't2', t2.* FROM t2 ... ;
INSERT INTO tmp SELECT 't3', t3.* FROM t3 ... ;

When you're done, the first column will indicate the name of the
table from which each row was obtained.
Thread
multi-table select (not a join)neal23 Sep
RE: multi-table select (not a join)neal23 Sep
  • RE: multi-table select (not a join)Paul DuBois23 Sep
    • RE: multi-table select (not a join)neal23 Sep
      • RE: multi-table select (not a join)Paul DuBois23 Sep
        • RE: multi-table select (not a join)neal23 Sep
          • RE: multi-table select (not a join)Paul DuBois23 Sep
        • RE: multi-table select (not a join)Paul DuBois23 Sep
    • Re: multi-table select (not a join)Jesse Sheidlower23 Sep
      • Re: multi-table select (not a join)Paul DuBois23 Sep
RE: multi-table select (not a join)Herman Verkade23 Sep