I have several tables that could possibly  have the same primary key.  I
have a procedure that would tell me if the primary key exists in any of the
tables.  I thought this SQL would work, but it returns no records if the
primary key is missing from any of the tables.

select adjr.adjr_id,
       branch.branch_id,
       carr.carr_id
from  adjr, branch, carr
where adjr.adjr_id(+) = 10048 and
      branch.branch_id(+) =10048 and
      carr.carr_id(+)=10048;

In my data, branch 10048 exists.  I was expecting a row with adjr_id and
carr_id both null and branch_id = 10048.  I actually get no rows returned.

What am I doing wrong?

--
Jim Poe (j...@fulcrumit.com)

--
Jim Poe (j...@fulcrumit.com)

This seems to work, but it ain't pretty:

select
  a.adjr_id, b.branch_id, c.carr_id
from
  (select decode(count(*), 0, null, 10048) adjr_id from adjr where adjr_id =
10048) a,
  (select decode(count(*), 0, null, 10048) branch_id from branch where
branch_id = 10048) b,
  (select decode(count(*), 0, null, 10048) carr_id from carr where carr_id =
10048) c;

Don,

Thanks for your reply.

I had another table that was related to all of them and my final solution
was
select office_id,
          adjr.adjr_id,
          branch.branch_id,
          carr.carr_id
from  office, adjr, branch, carr
where office_id=10048 and
      adjr.adjr_id(+) = office_id and
      branch.branch_id(+) =office_id and
      carr.carr_id(+)=office_id;

I still don't understand why the first select did not work.

--
Jim Poe (j...@fulcrumit.com)

Because it didn't join the tables.

John

Thanks

To determine which rows will be returned you need to use a table
without outer join condition.

In your first select, all tables in from clause was accessed by
outer join conditions, but there is no rows to return the outer
joined fields...

In this example, the table office will determine which rows will
be returned. You don't need to join the tables. It will depend
from your objectives.

[]s
Arthur

Jim Poe <j...@fulcrumit.com> escreveu nas notcias de mensagem:3b706152_1@dnews...