sdc248
asked on
SQL query with secondary filter clause
Hi:
I want to join tableA with table B, like the following:
select a.*, b.col1
from tableA a
left join tableB b on a.col1=b.col1 and a.col2=b.col2 --col2 is a datetime column
If the above brings back a matched (non-null) b.col1, I am fine, otherwise, I need to try to get it by:
select a.*, b.col1
from tableA a
left join tableB b on a.col1=b.col1 and a.col2=dbo.fnPriorBusiness Date(b.col 2)
Anyway to combine the above two into one queries and without join tableB twice? TableB is a huge table, anything I tried that involves joining it twice takes too long to run.
Thanks.
I want to join tableA with table B, like the following:
select a.*, b.col1
from tableA a
left join tableB b on a.col1=b.col1 and a.col2=b.col2 --col2 is a datetime column
If the above brings back a matched (non-null) b.col1, I am fine, otherwise, I need to try to get it by:
select a.*, b.col1
from tableA a
left join tableB b on a.col1=b.col1 and a.col2=dbo.fnPriorBusiness
Anyway to combine the above two into one queries and without join tableB twice? TableB is a huge table, anything I tried that involves joining it twice takes too long to run.
Thanks.
ASKER
Sorry, it should be b.col3.
I need a value from col3 of tableB.
I need a value from col3 of tableB.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. Didn't have chance to try your approach though. Used a workaround and got acceptable performance.
Should that be col2? col1 is the same in both tables.
If you don't need to list a specific value, just need to check if a match exists, you can use an EXISTS() instead of a join.