sqldb2

Combing two subqueries and use either of the value depends on which one is not null

VAC_ DOC

VAC_NRD

VAC_TRV

There tables are there VAC_NRD, VAC_DOC, VAC_TRV.

If this query return VAC_ID empty.

  1. SELECT VAC_ID FROM VAC_NRD WHERE VAC_NRD =?

Will use this query to get VAC_ID

2. (SELECT VAC_ID FROM VAC_DOC WHERE VAC_DOC =?)

Use the retrieved VAC_ID value here to fetch the final result

SELECT  * FROM VAC_TRV WHERE VAC_ID = ?

Is there any way I can achieve this using one query, by adding subquery or any other way.

SELECT  * FROM VAC_TRV WHERE VAC_ID =(SELECT VAC_ID FROM VAC_DOC WHERE VAC_DOC =?………)
Solution

  • You could try doing a full outer join of the two tables, then selecting the VAC_ID from the appropriate table using COALESCE():

    WITH cte AS (
    SELECT COALESCE(nrd.VAC_ID, doc.VAC_ID) AS VAC_ID
    FROM VAC_NRD nrd
    FULL OUTER JOIN VAC_DOC doc
        ON doc.VAC_ID = nrd.VAC_ID
    WHERE
        nrd.VAC_NRD = ? OR
        doc.VAC_DOC = ?
)

SELECT *
FROM VAC_TRV
WHERE VAC_ID IN (SELECT VAC_ID FROM cte);

    Note that we preferentially select the VAC_ID from the VAC_NRD source table, if it could be found there. Otherwise, we fall back to taking VAC_ID from the VAC_DOC table.