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 =?………)

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 =?………)

• sql
• db2

• There tables are there VAC_NRD, VAC_DOC, VAC_NRD. ... you listed VAC_NCR twice, is that a typo? If there is a third table, please provide the subquery for that one too. – Tim Biegeleisen Commented Nov 20, 2024 at 5:05
• It's a typo. updated now. – user630209 Commented Nov 20, 2024 at 5:08
• 1 Can't you simply write ...WHERE vac_id IN (COALESCE((SELECT VAC_ID FROM VAC_NRD WHERE VAC_NRD = ?),(SELECT VAC_ID FROM VAC_DOC WHERE VAC_DOC =?)))? – Jonas Metzler Commented Nov 20, 2024 at 5:50
• Please do not upload images of code/data/errors. – MatBailie Commented Nov 20, 2024 at 8:27
• Please read : Why should I provide a Minimal Reproducible Example, even for a very simple SQL query? – MatBailie Commented Nov 20, 2024 at 8:27

2 Answers 2

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.

you can use a CASE statement to decide which subquery to execute:

SELECT * FROM VAC_TRV WHERE VAC_ID = (
CASE 
    WHEN EXISTS (SELECT 1 FROM VAC_NRD WHERE VAC_NRD = ?) 
    THEN (SELECT VAC_ID FROM VAC_NRD WHERE VAC_NRD = ?)
    ELSE (SELECT VAC_ID FROM VAC_DOC WHERE VAC_DOC = ?)
END);