Is there a way to create an ‘indicator’ in an SQL(DB2) select query to indicate when a column is filled?

This title is vague as hell and doesn’t completely cover the problem, but I don’t know how else to word it. Basically, I have the following situation:

I have two tables:

Table 1: COMPANY. shows company parent-child structure

company	parent-company
A	NULL
B	A
C	A
D	A
E	B

Table 2: ACCOUNTS. accounts linked to companies

company	account-id	account-currency
B	1234	EUR
B	5678	GBP
C	7777	EUR

What I want is to retrieve the child companies of an input company, which for argument’s sake is company A. And of those child companies I want to know if there are any accounts linked to them. I do not want to know which ones, just if there are accounts linked to them.

My current query is as follows:

SELECT * 
FROM COMPANY A
left join ACCOUNTS B
ON A.company = B.company
WHERE A.parent-company = 'A'

This gives me the child companies linked to parent company A + every account linked to those child companies. This means that for company B I get two rows with two accounts.

company	parent-company	account-id	account-currency
B	A	1234	EUR
B	A	5678	GBP
C	A	7777	EUR
D	A	NULL	NULL

If I add a case statement to that query, to just signify if there is an account, the query looks like this:

SELECT A.company
      ,A.parent-company
      ,CASE WHEN (B.account-id IS NULL) AND (B.account-currency IS NULL)
       THEN 'N'
       ELSE 'Y'
       END AS account-present 
FROM COMPANY A
left join ACCOUNTS B
ON A.company = B.company
WHERE A.parent-company = 'A'

This results in the following table:

company	parent-company	account-present
B	A	Y
B	A	Y
C	A	Y
D	A	N

Seems like I’m getting somewhere but what I really want is this: one row per company with an indicator whether there are accounts.

company	parent-company	account-present
B	A	Y
C	A	Y
D	A	N

Does anyone know how to do this? I have tried multiple things but I can’t get my code to work.

Answer

Do you just want exists?

select c.company, c.parent_company,
       (case when exists (select 1 from accounts a where a.company = c.company)
             then 'Y' else 'N'
        end) as has_account
from company c
where c.parent_company = 'A';

Advertisement

Answer