Get results from the first query that has data-CodePudding

I have a requirement where I have to query three tables sequentially. But I only have to use results from the first query that has data.

SELECT *
FROM TABLE_A
WHERE MAC_ADDRESS IN ('MACADDRESS1', 'MACADDRESS2', 'MACADDRESS3');

SELECT *
FROM TABLE_B
WHERE MAC_ADDRESS IN ('MACADDRESS1', 'MACADDRESS2', 'MACADDRESS3')
AND TO_DATE(LAST_MODIFIED_DATE) >= DATEADD(DAY,-7,CURRENT_DATE());

SELECT *
FROM TABLE_C
WHERE MAC IN ('MACADDRESS1', 'MACADDRESS2', 'MACADDRESS3');

Case #1: all three tables return data. Requirement: use results from TABLE_A
Case #2: TABLE_B and TABLE_C return data. Requirement: use results from TABLE_B.
Case #3: TABLE_C returns data. Requirement: use results from TABLE_C.

I wrapped the three queries in three CTEs but struggling to implement the requirement. Any ideas how to implement this in SQL or would I have to use a programming language.

Appreciate any ideas!

CodePudding user response：

If all tables have the same structure then using UNION ALL and priority column could solve it:

WITH cte AS (
    SELECT 1 AS priority,*
    FROM TABLE_A
    WHERE MAC_ADDRESS IN ('MACADDRESS1', 'MACADDRESS2', 'MACADDRESS3');
    UNION ALL 
    SELECT 2 AS priority, *
    FROM TABLE_B
    WHERE MAC_ADDRESS IN ('MACADDRESS1', 'MACADDRESS2', 'MACADDRESS3')
    AND TO_DATE(LAST_MODIFIED_DATE) >= DATEADD(DAY,-7,CURRENT_DATE())
    UNION ALL
    SELECT 3 AS priority, *
    FROM TABLE_C
    WHERE MAC IN ('MACADDRESS1', 'MACADDRESS2', 'MACADDRESS3')
)
SELECT *
FROM cte
QUALIFY priority = MIN(priority) OVER();

CodePudding user response：

Option 1 - Executable block (SP or Anonymous block). Place your logic into anonymous bloc and execute

declare -- optional
begin
    Select A .....
    if @@rowcount > 0
    begin
      --- exit logic here
    end
    -- more of your logic here for B and C
end

Option 2 - UNION


Select A ...
UNION ALL
Select B ...
UNION ALL
Select C ...

For union to work, you need to have same number of columns in each select

If this is important to know where the data come from, you can add marker field


Select t.*, 'A' marker FROM TABLE_A t ...
UNION ALL
Select t.*, 'B' marker FROM TABLE_B t ...
. . . . . . .

Then you can tell where the first record come from - A, B or C