Here are two queries I have tried and failed:
1) select userid, case when exists(select * from bank where bankid = user.custid) then 'bank' else 'non-bank' end case from user 2) select userid, case when custid in (select bankid from bank) then 'bank' else 'non-bank' end case from user
SELECT DISTINCT userid, 'bank' FROM user, bank WHERE user.custid = bank.bankid UNION ALL SELECT DISTINCT userid, 'non-bank' FROM USER, BANK WHERE NOT EXISTS (SELECT 1 FROM bank WHERE user.custid = bank.bankid);This query performs an outer join. I did not use SQL outer join syntax but instead used the UNION formulation to get your bank/non-bank designation in the result set. You could instead issue the following query and in the program where an actual value is returned for bankid then substitute 'bank' and where a NULL is returned substitute 'non-bank'.
SELECT user.userid, bank.bankid FROM user LEFT OUTER JOIN bank ON user.custid = bank.bankid;
This was first published in January 2003