Conditional where...?

[calgarychinese]

Hi there
I'm trying to generate a report. User will give a date as a filter.

There are 2 tables to consider, A and B. Both contain a date field against which the user-provided date is checked.

I want to do it so if the record for an employee is found in B, then use the B's date field as the where clause. If it's not found, then use A's date field as the where clause.

So.....
select A.*, B.*
from tblA A, tblB B
WHERE
-- if exists (select * from tblB where B.EmpID=123) then B.DateValue = %USERDATE%
-- else A.DateValue = %USERDATE%

Is it doable?

Thanks
CC

 

[SQLSister]

Look in BOL at the CASE Statement

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[bborissov]

How you join these tables?

SELECT * 
       from TblA
    LEFT JOIN TblB ON TblA.EMpNo = TblB.EMpNo
    WHERE (TblB.DateValue IS NULL AND TblA.DateValue =  %USERDATE%) OR
          (TblB.DateValue IS NOT NULL AND TblB.DateValue =  %USERDATE%)

Borislav Borissov

 

[calgarychinese]

bborissov

THANKS! I didn't know you can do that.

One question though, in my case DateValue is mandatory in both tables A and B, so what you did is fine, but if it is an optional field, then the part where you check against NULL is it true I'll replace that field by some mandatory field, such as the PK field? So it'll look like

SELECT *
from TblA
LEFT JOIN TblB ON TblA.EMpNo = TblB.EMpNo
WHERE (TblB.ID IS NULL AND TblA.DateValue = %USERDATE%) OR
(TblB.ID IS NOT NULL AND TblB.DateValue = %USERDATE%)

 

[bborissov]

Yes, It should work.

Borislav Borissov

 

[calgarychinese]

Thanks a lot.
Didn't expect to get a solution so fast

THanks
CC

 

[bborissov]

Borislav Borissov