Solved

SQL query with secondary filter clause

Posted on 2013-12-19
4
247 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
  • 2
  • 2
4 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
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 69

Accepted Solution

by:
ScottPletcher earned 500 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now