How to check if a specific id exists in multiple tables and which ones with Oracle?

I have a table named persons which the primary key is pi开发者_开发百科d than I have 3 different tables (let's call them A, B and C) where pid is a foreign key.

I need to know if a specific pid exists in any of those tables and exactly which of them.

I don't know if this is achievable with a single query but I'm thinking that a function would work better in my specific case so that I could do this:

SELECT pid, name, sex, func_name(pid) my_str
FORM persons
WHERE pid = 5

And then, depending on the tables that pid is found, my_str would be something like:

B | C

Meaning pid = 5 was found on tables B and C.

I could do this with 3 SELECTs in the function, using COUNT(*). If the result is > 0, then pid = 5 was found on the table from the respective SELECT. But this seems a stupid way to do it, suggestions?

Would something like the following do the trick for you? I just added case/whens to show you how you could test for existance in the other tables.

select p.pid
      ,p.name
      ,p.sex
      ,case when a.pid = p.pid then 'Yes' else 'No' end as in_a
      ,case when b.pid = p.pid then 'Yes' else 'No' end as in_b
      ,case when c.pid = p.pid then 'Yes' else 'No' end as in_c
  from persons p
  left outer join a on (a.pid = p.pid)
  left outer join b on (b.pid = p.pid)
  left outer join c on (c.pid = p.pid)
 where p.pid = 5;

An enhancement to @Ronnis 's answer (I didn't test it, but I hope something like this will work. If not, forgive me for the late hour :) )

select p.pid,
       p.name,
       p.sex,
       case when a.pid = p.pid then 'A' else '' end |
       case when b.pid = p.pid then 'B' else '' end |
       case when c.pid = p.pid then 'C' else '' end
  from persons p
  left outer join a on (a.pid = p.pid)
  left outer join b on (b.pid = p.pid)
  left outer join c on (c.pid = p.pid);