Join another table if no match with first table-CodePudding

I have a LEFT JOIN to a table #tempVCCurrent, and for any row that there is no match and comes out to NULL, try and join a second table #tempVCAll. I don't want extra columns from the second table since both temp tables have exactly the same columns.

Basically I want to prefer the first table #tempVCCurrent since that table has the most recent data, but if no record found try #tempVCAll. Any easy way to do this?

select *
from mytable st
inner join mytableline stl on st.transfer_id = stl.transfer_id
left join #tempVCCurrent vcur on st.from = vcur.number
--if above left join no match and comes to null, do the below
left join #tempVCAll vc on st.from = vc.number

CodePudding user response：

Create a CTE which unions the two joined tables together. You can then join this instead. Here's some pseudo code:

;WITH cte AS(
SELECT col1, col2, col3
  FROM firstTable
UNION
SELECT col1, col2, col3
  FROM secondTable
)

SELECT *
  FROM mainTable mt
    LEFT OUTER JOIN CTE
      ON mt.col1 = CTE.col1

If you can provide some detailed test data I'll be happy to help refine the example.

CodePudding user response：

I think you're trying to get result like:

IF LEFT JOIN to #tempVCCurrent gets NULL
THEN
    JOIN to #tempVCAll

You may think this as 2 phrases in your query:

WITH Phase1
AS (SELECT
  *
FROM mytable st
INNER JOIN mytableline stl
  ON st.transfer_id = stl.transfer_id
LEFT JOIN #tempVCCurrent vcur
  ON st.[from] = vcur.number
WHERE vcur.number IS NULL)
SELECT
  *
FROM Phase1 p1
LEFT JOIN #tempVCAll vc
  ON p1.[from] = vc.number

With query inside CTE as phase 1 and query outside CTE as phase 2. This will help you understanding your query in the future in case you need to review or expand your query in the future.

Of course, you may need to limit columns and add index(es) to improve the performance of your query.