[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

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.fnPriorBusinessDate(b.col2)

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.
0
sdc248
Asked:
sdc248
  • 2
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
select a.*, b.col1

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.
0
 
sdc248Author Commented:
Sorry, it should be b.col3.

I need a value from col3 of tableB.
0
 
Scott PletcherSenior DBACommented:
Hopefully SQL can figure out the best plan possible for the code below.  If not, if it's still too slow, post definitions for all indexes on tableB.


select a.*,
    (SELECT TOP (1) b.col3
     FROM tableB b
     WHERE a.col1=b.col1 and a.col2 in (b.col2, dbo.fnPriorBusinessDate(b.col2))
     ORDER BY CASE WHEN a.col2 = b.col2 THEN 1 ELSE 2 END
    ) AS col3    
from tableA a
0
 
sdc248Author Commented:
Thanks. Didn't have chance to try your approach though. Used a workaround and got acceptable performance.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now