Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL query with secondary filter clause

Posted on 2013-12-19
4
Medium Priority
?
275 Views
Last Modified: 2014-01-03
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
Comment
Question by:sdc248
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39730422
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
 

Author Comment

by:sdc248
ID: 39730462
Sorry, it should be b.col3.

I need a value from col3 of tableB.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39730488
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
 

Author Comment

by:sdc248
ID: 39755110
Thanks. Didn't have chance to try your approach though. Used a workaround and got acceptable performance.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question