asked on

Simple exists query within select

Simple select query, but one field needs to return a 'Y' if a record exists in a field in another table. Having trouble with the exists syntax.
Example:

Select a.main, a.other, b.something
from b inner join
a on b.main = a.main
where a.criteria = 'x'

Need to add a field like: 'Y' as Flag where exists (select c.sub from c)
so that results would be:
Main Other Something Flag
------- ------- --------------- -----
Main, Other, Something, Y
Main, Other, Something, (can be null or 'N')
Main, Other, Something, (can be null or 'N')
Main, Other, Something, (can be null or 'N')
Main, Other, Something, Y
Main, Other, Something, Y

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

smilieface

Try this one for size

Select a.main, a.other, b.something, case c.<mandatoryColumn> when not null the 'Y' else 'N' end
   from b
   inner join a
      on b.main = a.main
   left outer join c 
      on  <criteria>
   where a.criteria = 'x'

Open in new window

Doing an inline sub-select means that for each row in the main query, an additional query will be executed. It will work, but be slow. Doing a left join will be quicker

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Charlene Kerr

ASKER

Scott, yes, the join SQL does with the subquery works, and the join referenced above won't work. Thanks for your help.