WITH DetailsFor744 AS
(
SELECT SalesOrderID FROM
Sales.SalesOrderDetail
WHERE ProductID=744
),
MakeCall AS
(
SELECT SalesPersonID FROM
Sales.SalesOrderHeader
WHERE SalesOrderID in 
(SELECT SalesOrderID FROM DetailsFor744)
)
SELECT SalesPersonID,
CASE
WHEN SalesPersonID in 
(SELECT SalesPersonID FROM MakeCall) 
THEN 'Make Call!'
ELSE 'Relax'
END AS Message
FROM Sales.SalesPerson;
a
b
c
d
