CODEWITHSUNDEEP
sqloracle-database
Surya Paramesh S
Surya Paramesh S

Reputation: 33

How to return boolean if record exists for multiple tables

I would like to return boolean value if the record exists. The below query works fine for single table.

SELECT CASE WHEN MAX(componentid) IS NULL THEN 'NO' ELSE 'YES' END
table3 FROM table3 WHERE componentid = 'GetAccountBalance';

 

I have tried the same for 3 tables using JOIN but I couldn't achieve the expected result.The below query returns all the values as "No" if any of the table (suppose table3) doesn't have record while the other two table has.

select CASE WHEN MAX(a.componentid)IS NULL THEN 'NO' ELSE 'YES' END table1,
CASE WHEN MAX(b.componentid)IS NULL THEN 'NO' ELSE 'YES' END table2,
CASE WHEN MAX(c.componentid)IS NULL THEN 'NO' ELSE 'YES' END table3
from table1 a
join table2 b on a.componentid=b.componentid 
join table3 c on a.componentid=c.componentid 
and a.componentid ='GetAccountBalance';

Output

table1 table2 table3
NO     NO     NO

expected

table1 table2 table3
YES    YES    NO

Also would it be possible to search for multiple values using in? like

a.componentid in ('GetAccountBalance','GetCreditBalance')

Upvotes: 3

Views: 3227

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You should phrase this as exists:

select (case when exists (select 1 from table1 where componentid = 'GetAccountBalance')
             then 'YES' else 'NO'
        end) as flagTable1,
       (case when exists (select 1 from table2 where componentid = 'GetAccountBalance')
             then 'YES' else 'NO'
        end) as flagTable2,
       (case when exists (select 1 from table3 where componentid = 'GetAccountBalance')
             then 'YES' else 'NO'
        end) as flagTable3
from dual;

The overhead of doing joins is simply unnecessary. The above should also make optimal use of indexes on the tables.

EDIT:

For multiple components, you can use correlated subqueries:

select (case when exists (select 1 from table1 t1 where t1.componentid = c.componentid)
             then 'YES' else 'NO'
        end) as flagTable1,
       (case when exists (select 1 from table2 t2 where t2.componentid = c.componentid)
             then 'YES' else 'NO'
        end) as flagTable2,
       (case when exists (select 1 from table3 t3.where t3.componentid = c.componentid)
             then 'YES' else 'NO'
        end) as flagTable3
from (select 'GetAccountBalance' as componentid from dual union all
      select 'GetCreditBalance' from dual
     ) c

Upvotes: 2

user330315
user330315

Reputation:

You want an outer join on all three tables. You also need to include a condition that checks the presence of the value in all three tables:

select coalesce(a.componentid, b.componentid, c.componentid) as componentid,
       case when a.componentid is null then 'no' else 'yes' end as in_table1,
       case when b.componentid is null then 'no' else 'yes' end as in_table2,
       case when c.componentid is null then 'no' else 'yes' end as in_table3
from table1 a
  full outer join table2 b on a.componentid = b.componentid
  full outer join table3 c on b.componentid = c.componentid
where a.componentid in ('GetAccountBalance','GetCreditBalance')
   or b.componentid in ('GetAccountBalance','GetCreditBalance')
   or c.componentid in ('GetAccountBalance','GetCreditBalance');

If you only use where a.componentid in ('GetAccountBalance','GetCreditBalance') the result would not have any rows if the values didn't exist at all in table1.

This will not return rows for values that are in none of the tables at all!

If componentid is not unique in each table, you might get multiple rows for each table.

SQLFiddle example: http://sqlfiddle.com/#!4/c70b3e/1
(The example uses numbers for componentid because I was too lazy to type the strings)

Upvotes: 1

Md. Shamim Al Mamun
Md. Shamim Al Mamun

Reputation: 386

Please find the below query:

select CASE WHEN MAX(a.componentid)IS NULL THEN 'NO' ELSE 'YES' END table1,
CASE WHEN MAX(b.componentid)IS NULL THEN 'NO' ELSE 'YES' END table2,
CASE WHEN MAX(c.componentid)IS NULL THEN 'NO' ELSE 'YES' END table3
from table1 a
join table2 b on a.componentid=b.componentid 
left outer join table3 c on a.componentid=c.componentid 
and a.componentid ='GetAccountBalance';

Yes you can use : and a.componentid in ('GetAccountBalance','GetCreditBalance')

It will help you.

Upvotes: 0

Related Questions