Re: Query Table Name
Date: Mon, 3 Nov 2008 13:23:13 -0800 (PST)
Message-ID: <2f80a81b-b248-49f6-9d45-c020fd434f38@b38g2000prf.googlegroups.com>
On Nov 3, 3:13 pm, artme..._at_gmail.com wrote:
> On Nov 3, 3:09 pm, joel garry <joel-ga..._at_home.com> wrote:
>
>
>
>
>
> > On Nov 3, 12:55 pm, artme..._at_gmail.com wrote:
>
> > > If you are joining one or more tables, is there a way to know which
> > > table the value came from? For example:
>
> > > SELECT DISTINCT(id),
> > > DECODE(<table_name>,value,<table_name>,value,<table_name>,value)........
> > > FROM a, b, c
> > > WHERE a.id = b.id
> > > AND a.id = c.id;
>
> > > In this same query, can I get the table name the name of the table the
> > > value actually came from?
>
> > Can you give an example where this works without error? If you are
> > getting an error, care to share? Could the value include the table?
>
> > jg
> > --
> > @home.com is bogus.
> > Send it home to mother in a cardboard box.
>
> Well, basically I have 3 tables with an ID column. I want a distinct
> list of the ID column from the 3 tables, but I also want to know which
> table it came from so I can perform a delete and such......
>
> I know I can do this in several queries and with some PL/SQL code, but
> I was wondering if I can get the information I need, DISTINCT(id) from
> all tables, plus the table name, all in one query........
>
> The query is basically what you see above. I just am curious if I can
> get the table name as part of the query.......unless I need to make
> some complex, multi-level query....- Hide quoted text -
>
> - Show quoted text -
Including the table name will make every ID value distinct:
select distinct table_name, id,
DECODE(<table_name>,value,<table_name>,value,<table_name>,value)........
from
(select 'A' table_name, id from a) a, (select 'B' table_name, id from
b) b, (select 'C' table_name, id from c) c
WHERE a.id = b.id
AND a.id = c.id;
so having ID value 1 in all three tables returns three distinct records for that one ID.
I'm thinking the PL/SQL route is your best choice.
David Fitzjarrell Received on Mon Nov 03 2008 - 15:23:13 CST