I need to specify the flag - 0 if the condition does not match, 1 if it is - and I can do this in two different ways:
Get the employee ID, name, 1, if there are others in the suborder - 0, if not:
SELECT e.ID
, e.Name
, ISNULL ( ( SELECT TOP 1 1 FROM Employee se WHERE se.ManagerID = e.ID ) , 0 ) AS HasSubordinates
FROM Employee e
or
SELECT e.ID
, e.Name
, ISNULL ( ( SELECT 1 WHERE EXISTS ( SELECT * FROM Employee se WHERE se.ManagerID = e.ID ) ) , 0 ) AS HasSubordinates
FROM Employee e
Which version would you choose and why?
Update 1
How about this?
SELECT o.ID
, o.Name
, o.StartDate
, o.EndDate
, ISNULL ( ( SELECT TOP 1 1 FROM changes c WHERE c.ChangeDate BETWEEN o.StartDate AND o.EndDate ) , 0 ) AS IsChanged
FROM Orders o
or
SELECT o.ID
, o.Name
, o.StartDate
, o.EndDate
, ISNULL ( ( SELECT 1 WHERE EXISTS ( SELECT * FROM changes c WHERE c.ChangeDate BETWEEN o.StartDate AND o.EndDate ) ), 0 ) AS IsChanged
FROM Orders o