if exists

[mjd3000]

I need to change my SQL below so that for the second condition (and exists...etc) it checks if it exists and if it does exist then the value of a particular field must be 1.

Any ideas how to do this?

(select count(*)
from filteredlead l1
inner join filteredactivitypointer a
on l1.leadid = a.regardingobjectid
where a.activitytypecode in (4201,4401)
and convert(char(10),l1.createdonutc,103) = convert(char(10),getdate()-1,103) and exists (select 1

from filteredlead l
inner join filteredactivitypointer a
on l.leadid = a.regardingobjectid
inner join filteredphonecall p
on a.activityid = p.activityid
where a.activitytypecode = 4210
and p.new_calloutcome in (1,3,4,5,6,7)
and convert(char(10),l.createdonutc,103) = convert(char(10),getdate()-1,103) and l.leadid = l1.leadid))
as 'New Contact Achieved',

 

[bborissov]

Then its place is not in the WHERE clasue but in the fields area:

SELECT ....,
       CASE WHEN EXISTS(SELECT .....)
                 THEN 1
            ELSE 0 END AS SomeName
FROM ...
WHERE (w/o EXISTS clause)

Other way is to use that SELECT as derived table, LEFT join it to the main one and check if the field IS NULL.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Aha - interesting

See also

http://forums.asp.net/t/1442679.aspx