Help coderanch get a
new server
by contributing to the fundraiser
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

EXISTS/UNION Combination

 
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
select
t1.ACCT_ID
from
account t1,
where exists
(select t2.ACCT_ID
from
exempt_account t2
where
t2.CTF_ENTY_ID = ?
and t2.EXMPT_STRT_DT = ?
and t2.EXMPT_END_DT = ?)
order by t1.ACCT_ID asc;

Problem:
I have to extract recs from exempt_account table by matching the enty_id from the enty_id that's passed in (refer to ? above) + comparing Strt_dt + End_dt dates to dates I passed in (refer to ? above).
If there is a match I then want to use enty_id to match to a rec on the account table where I capture the acct_id.
If there is no match to the exempt_account table I want to use the enty_id
that's passed in and match directly to the account table.
If rec exists in exempt_account table it's considered to be 'exempt' and I need to be able to create a field that I can later test to determine if it came from exempt_account table or just account table.
If it only exists in account table It's non-exempt.
 
Rocko Smith
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Should I use a union for the 2 tables because this only works if there is a record in the exempt_account table?
 
I'd appreciate it if you pronounced my name correctly. Pinhead, with a silent "H". Petite ad:
We need your help - Coderanch server fundraiser
https://coderanch.com/t/782867/Coderanch-server-fundraiser
reply
    Bookmark Topic Watch Topic
  • New Topic