How To Write a Case Statment to check two tables

[Brian Connelly]

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?

[Rabbit]

Outer join the two tables and use coalesce on the two fields to return the one with data.

[Brian Connelly]

Do you have a small sample of how to do that? Im kind of new to advance SQL.

[Rabbit]

It would be something like this:

SELECT *
tableA FULL OUTER JOIN tableB
ON tableA.ID = tableB.ID
WHERE COALESCE(tableA.ID, tableB.ID) = 12345

[ariful alam]

You can check the following:

if exists (select * from tableA where id = 1234)
begin
   select * from tableA where id = 1234
end
else if exists (select * from tableB where id = 1234)
begin
   select * from tableB where id = 1234
end
else
begin
   select 'answer'=0
end