Multiple Joins with one result from one of the joins - MS SQL 2014

I have a procedure that has multiple left outer joins because of the three left outer joins only one of the three joins will return data.  The procedure looks at a baseprice table and two discountprice tables but only one of the three will return data.  I am trying to write a query that will allow the amount and begin & end dates be returned for the join that has the data.  I have thought of doing a union between the 3 pricing tables because it will return only the rows that I need as of today (GETDATE()).  Then just do one Join on the result table and be set to go.  I did not know if there was a more efficient way of doing this or not?  Does this make sense?

Here is a copy of the sql script I am running.

SELECT bm.UPC
      ,AmountReturned   -- from one of the 3 left outer joins
      ,.BeginDate               -- from one of the 3 left outer joins
      ,EndDate                   -- from one of the 3 left outer joins
FROM dbo.SAL_BarcodeMaster bm
      JOIN dbo.SAL_SKUMaster sm ON
            sm.SYS_SLIM = bm.SYS_SLIM
      LEFT OUTER JOIN dbo.SAL_BasePrice bp ON
            bp.SYS_SLIM = sm.SYS_SLIM
            AND bp.StoreNumber = sm.StoreNumber
            AND ((GETDATE() >= bp.BeginDate AND GETDATE() <= bp.EndDate)
                  OR (GETDATE() >= bp.BeginDate AND bp.EndDate = '1900-01-01'))
      LEFT OUTER JOIN dbo.SAL_DiscountPriceNC dn ON
            dn.SYS_SLIM = sm.SYS_SLIM
            AND dn.StoreNumber = sm.StoreNumber
            AND ((GETDATE() >= dn.BeginDate AND GETDATE() <= dn.EndDate)
                  OR (GETDATE() >= dn.BeginDate AND dn.EndDate = '1900-01-01'))
      LEFT OUTER JOIN dbo.SAL_DiscountPriceWC dw ON
            dw.SYS_SLIM = sm.SYS_SLIM
            AND dw.StoreNumber = sm.StoreNumber
            AND ((GETDATE() >= dw.BeginDate AND GETDATE() <= dw.EndDate)
                  OR (GETDATE() >= dw.BeginDate AND dw.EndDate = '1900-01-01'))

Here are the three select statements with the columns that are returned that I was going to use for the UNION of the three tables:


SELECT  bp.RECID AS SYS_SLBP,bp.ITEMNUMBER AS SYS_SLIM, bp.StoreNumber AS StoreNumber
      ,bp.DATAAREAID AS CompanyID
      ,CAST(ACTIVEPRICE AS DECIMAL(14,6)) AS SignLabelRetail,CURRENCY AS Currency
      ,CAST(STARTDATE AS DATE) AS BeginDate, CAST(ENDDATE AS DATE) AS EndDate
FROM MicrosoftDynamicsAx.dbo.PSP_BASEPRICECHANGELOG bp
WHERE       ((GETDATE() >= bp.BeginDate AND GETDATE() <= bp.EndDate)
            OR (GETDATE() >= bp.BeginDate AND bp.EndDate = '1900-01-01'))


SELECT RECID AS SYS_SLDNC, ITEMNUMBER AS SYS_SLIM, StoreNumber AS StoreNumber
      ,DATAAREAID AS CompanyID, CAST(DISCAMOUNT AS DECIMAL(14,6)) AS DiscountAmount
      ,CAST(DISCPCT AS DECIMAL(14,6)) AS DiscountPercent, CURRENCY AS Currency
      ,CAST(STARTDATE AS DATE) AS BeginDate, CAST(ENDDATE AS DATE) AS EndDate
FROM MicrosoftDynamicsAx.dbo.PSP_NONPPCDISCOUNTCHANGELOG nd
WHERE       ((GETDATE() >= nd.BeginDate AND GETDATE() <= nd.EndDate)
            OR (GETDATE() >= nd.BeginDate AND nd.EndDate = '1900-01-01'))


SELECT RECID AS SYS_SLDNC, ITEMNUMBER AS SYS_SLIM, StoreNumber AS StoreNumber
      ,DATAAREAID AS CompanyID, CAST(DISCAMOUNT AS DECIMAL(14,6)) AS DiscountAmount
      ,CAST(DISCPCT AS DECIMAL(14,6)) AS DiscountPercent, CURRENCY AS Currency
      ,CAST(STARTDATE AS DATE) AS BeginDate, CAST(ENDDATE AS DATE) AS EndDate
FROM MicrosoftDynamicsAx.dbo.PSP_DISCOUNTCHANGELOG d
WHERE       ((GETDATE() >= d.BeginDate AND GETDATE() <= d.EndDate)
            OR (GETDATE() >= d.BeginDate AND d.EndDate = '1900-01-01'))
j_heckAsked:
Who is Participating?
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.

Kyle AbrahamsSenior .Net DeveloperCommented:
The left joins will give you nulls for the tables where the entries don't exist.  You can
coalesce them, thereby giving you the non, null result from each table, regardless of the table.

eg:
Coalesce(bp.AmountReturned  , dn.AmountReturned , dw.AmountReturned  ) as AmountReturned  
etc.
0

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
PortletPaulfreelancerCommented:
I would challenge this assertion, emphasis added:

>>"because of the three left outer joins only one of the three joins will [ever] return data"

So you cannot have a base price and a discounted price at the same time?
That's very strange commercial behaviour.

Are you absolutely certain you only EVER get one price, from one table, for any item, at any time?
0
Scott PletcherSenior DBACommented:
You can UNION within OUTER APPLY to limit the matches to at max 1 row.  If you need to, you can add a "sort_sequence" to the derived table so that if multiple rows are returned, you can specify which of them you want:

SELECT bm.UPC
      ,price.AmountReturned
      ,price.BeginDate
      ,price.EndDate
FROM dbo.SAL_BarcodeMaster bm
      JOIN dbo.SAL_SKUMaster sm ON
            sm.SYS_SLIM = bm.SYS_SLIM
      OUTER APPLY (
          SELECT TOP (1) AmountReturned, BeginDate, EndDate
          FROM (
              SELECT AmountReturned, BeginDate, EndDate
              FROM dbo.SAL_BasePrice bp
              WHERE
                  bp.SYS_SLIM = sm.SYS_SLIM
                  AND bp.StoreNumber = sm.StoreNumber
                  AND (GETDATE() >= bp.BeginDate AND (GETDATE() <= bp.EndDate OR bp.EndDate = '19000101'))
              UNION ALL
              SELECT AmountReturned, BeginDate, EndDate
              FROM dbo.SAL_DiscountPriceNC dn
              WHERE
                  dn.SYS_SLIM = sm.SYS_SLIM
                  AND dn.StoreNumber = sm.StoreNumber
                  AND (GETDATE() >= dn.BeginDate AND (GETDATE() <= dn.EndDate OR dn.EndDate = '19000101'))
              UNION ALL
              SELECT AmountReturned, BeginDate, EndDate
              FROM dbo.SAL_DiscountPriceWC dw
              WHERE
                  dw.SYS_SLIM = sm.SYS_SLIM
                  AND dw.StoreNumber = sm.StoreNumber
                  AND (GETDATE() >= dw.BeginDate AND (GETDATE() <= dw.EndDate OR dw.EndDate = '19000101'))
          ) AS derived_table
      ) AS price ( AmountReturned, BeginDate, EndDate )
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
j_heck, do you still need help with this question?
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.