By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,287 Members | 1,561 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,287 IT Pros & Developers. It's quick & easy.

How To Write a Case Statment to check two tables

100+
P: 103
I have a Invoice Number that can appear in either two tables (history = table A and current= table B) which have all the same fields, but not both tables at one time. I want to check If the invoice is in table A and if so then grab other fields, if not check table B. How would I write a Case statement of If Statement for this?
Jul 16 '12 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,430
Outer join the two tables and use coalesce on the two fields to return the one with data.
Jul 16 '12 #2

100+
P: 103
Do you have a small sample of how to do that? Im kind of new to advance SQL.
Jul 31 '12 #3

Rabbit
Expert Mod 10K+
P: 12,430
It would be something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. tableA FULL OUTER JOIN tableB
  3. ON tableA.ID = tableB.ID
  4. WHERE COALESCE(tableA.ID, tableB.ID) = 12345
Jul 31 '12 #4

ariful alam
100+
P: 185
You can check the following:
Expand|Select|Wrap|Line Numbers
  1. if exists (select * from tableA where id = 1234)
  2. begin
  3.    select * from tableA where id = 1234
  4. end
  5. else if exists (select * from tableB where id = 1234)
  6. begin
  7.    select * from tableB where id = 1234
  8. end
  9. else
  10. begin
  11.    select 'answer'=0
  12. end
Aug 4 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.