Re: Query Table Name

artmerar_at_gmail.com writes:

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

I don't get what that decode statement is supposed to be doing.

I also am a bit confused by your specification. You have

>> > WHERE a.id = b.id
>> >     AND a.id = c.id;

Now, this means that you will only get rows back for IDs that are in table a AND are also in both table b and c, so by definition, the IDs returned are in all three tables. A clue to this can be seen from the fact that distinct(id) doesn't raise any errors regarding ambiguous column specifications.

I suspect what you may really want is som form of outer join if you expect the value your joining on to not be in some tables, but thats a guess at your intentions and cold easily be wrong.

Tim

-- 
tcross (at) rapttech dot com dot au