Sunday, August 9, 2009

Correlated Subquery with Exists

I was updating a query the other day that was screening unwanted duplicates by using a Group By statement on all fields. The goal of the query is to return all records if the search parameter is null, otherwise filter the records by the search parameter. After a little analysis, I realized the duplicates were coming from a join on a Child table where the child table fields were only used for the where clause. The solution I came up with was to use a correlated subquery with Exists in the where clause like:

WHERE
(
(@ChildField IS NULL)
OR EXISTS
(
Select
1
from
ChildTable CT
where
CT.ID = TargetTable.ID
and CT.ChildField like @ChildField
)
)

No comments:

Post a Comment