Combine validation with count and value from different tables

November 20, 2023

I have two select statements that query from two different tables

select count(1) from ACCOUNTS WHERE CUSTOMER_ID=100206 HAVING COUNT (*) = 0
select ATTEMPTED from DISC_NATURE WHERE CUSTOMER_ID=100206 AND ATTEMPTED='NO';

Wanted to combine two select statement in a single validation. The validation will only be TRUE when ACCOUNTS table is empty and ATTEMPTED=NO

>Solution :

Use CROSS JOIN:

SELECT *
FROM   ( select count(1) AS num_accounts
         from   ACCOUNTS
         WHERE  CUSTOMER_ID=100206
         HAVING COUNT (*) = 0 ) a
       CROSS JOIN
       ( select ATTEMPTED
         from   DISC_NATURE WHERE CUSTOMER_ID=100206
         AND    ATTEMPTED='NO' ) d;

Then you will only get an output if there are no accounts and no attempted.

Or use a CASE expression:

SELECT CASE
       WHEN NOT EXISTS (
              SELECT 1
              FROM   accounts
              WHERE  CUSTOMER_ID=100206
            )
       AND  EXISTS (
              select 1
              from   DISC_NATURE WHERE CUSTOMER_ID=100206
              AND    ATTEMPTED='NO'
            )
       THEN 'No account and no attempt'
       ELSE 'Either account or attempt'
       END AS has_account_attempt
FROM   DUAL;

And you will always get 1 row with a description of the result.