SQL Join only if field is not null

I need to grab the PANEL_START_TIME values from a table and I have records in the table where the PANEL_PROC_ID may or may not be null (there are other criteria involved, but that's not the point here).  Examples:

LOG_ID      PANEL_START_TIME PANEL_PROC_ID
200046      05/27/14 13:11      
200046      05/27/14 13:47      
200048      08/22/14 08:12      1070002291
200048      08/22/14 08:36      1070002291

I can get what I want with these two joins:

                      LEFT OUTER JOIN
                      OR_LOG_PANEL_TIMES AS OLPTProcStart1 ON
                      OLPTProcStart1.LOG_ID = OLAP1.LOG_ID AND
                      OLPTProcStart1.PANEL_TIME_EVENT_C = 10 AND
                      OLPTProcStart1.PANEL_NUMBER = OLAP1.ALL_PROCS_PANEL AND
                      OLPTProcStart1.PANEL_PROC_ID = OLAP1.OR_PROC_ID

                      LEFT OUTER JOIN
                      OR_LOG_PANEL_TIMES AS OLPTProcStart1X ON
                      OLPTProcStart1X.LOG_ID = OLAP1.LOG_ID AND
                      OLPTProcStart1X.PANEL_TIME_EVENT_C = 10 AND
                      OLPTProcStart1X.PANEL_NUMBER = OLAP1.ALL_PROCS_PANEL AND
                      OLPTProcStart1X.PANEL_PROC_ID IS NULL

... and then use this in the SELECT statement:

   COALESCE(OLPTProcStart1.PANEL_START_TIME,OLPTProcStart1X.PANEL_START_TIME)  AS 'P1_Start'

But banging on the OR_LOG_PANEL_TIMES so many times (I have to do this 16 times, in fact) really slows down the query.

Question: Is there a way to combine these two joins into a single join?
LVL 1
curtwadeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
Eliminate the last LEFT OUTER JOIN.

Change the last criteria on your first join from:

OLPTProcStart1.PANEL_PROC_ID = OLAP1.OR_PROC_ID
to:

ISNULL(OLPTProcStart1.PANEL_PROC_ID, OLAP1.OR_PROC_ID) = OLAP1.OR_PROC_ID
Let me know how that works for you. :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
curtwadeAuthor Commented:
Yes!  This works just fine, dsacker:

                      LEFT OUTER JOIN
                      OR_LOG_PANEL_TIMES AS OLPTProcStart1 ON
                      OLPTProcStart1.LOG_ID = OLAP1.LOG_ID AND
                      OLPTProcStart1.PANEL_TIME_EVENT_C = 10 AND
                      OLPTProcStart1.PANEL_NUMBER = OLAP1.ALL_PROCS_PANEL AND
                      ISNULL(OLPTProcStart1.PANEL_PROC_ID, OLAP1.OR_PROC_ID) = OLAP1.OR_PROC_ID

And while driving home I had a 'D'oh!' moment and this works, too:

                      LEFT OUTER JOIN
                      OR_LOG_PANEL_TIMES AS OLPTProcStart1 ON
                      OLPTProcStart1.LOG_ID = OLAP1.LOG_ID AND
                      OLPTProcStart1.PANEL_TIME_EVENT_C = 10 AND
                      OLPTProcStart1.PANEL_NUMBER = OLAP1.ALL_PROCS_PANEL AND
                      (OLPTProcStart1.PANEL_PROC_ID = OLAP1.OR_PROC_ID OR OLPTProcStart1.PANEL_PROC_ID IS NULL)

I ran each three times and my method beat yours by an average of 10 seconds (with average run time of ~60 seconds for all trials), so you only get 499 points.  ;-)

Thanks for your quick reply!
dsackerContract ERP Admin/ConsultantCommented:
But the extra point was for inspiration, though, eh? *lol*

All the best. :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.