Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    11

    Unanswered: using an exists in, inside of a decode

    I have two tables tblA and tblB
    each has a field named id
    all of the id records in tblB are in tblA, but not all of the records in tblA are in tblB.

    What I want to do in my query is have a field that returns if the id in tblA is in tblB.

    Here is what I have for a query


    Code:
    Select tblA.id,
             decode (tblA.id, tblA.id IN (select id from tblB), 'Exists',
                                                                                , 'Does not exist'),
             tblA.cost
    From tblA;
    It doesn't work quite right. Does anyone know how to tweak it to get it to?

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    Just a shot, but try an outer join like so -
    Code:
    select a.id, 
    decode(a.id, b.id, 'EXISTS', 'NOT EXISTS')
    from a,b
    where a.id = b.id (+)
    Since you outer join, you grab all ids from A, and any matching from B. If the id from A and B match, obviously it is in both tables. If the id is not in B, matching A.ID to null will return NOT EXISTS.

    Hope this helps!
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Jan 2005
    Posts
    11
    Thank you soooooo much

    I was trying to maker it hareder than it needed to, and the outer join is all I really need to do. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •