SQL Server NOT EXISTS from more than one table

Question

Hi:

I need to return rows if exists, if not return which one of the passed value is NOT EXISTS:

DECLARE @INPUT1 BIGINT
DECLARE @INPUT2 BIGINT
DECLARE @INPUT3 BIGINT

SELECT e.Name, d.Name, c.Name
   FROM Employee e
   JOIN Department d ON e.DeptID = d.DeptID
   JOIN City c ON e.CityID = c.CityID
WHERE
   e.EmpID = @INPUT1
   AND d.DeptID = @INPUT2
   AND c.CityID = @INPUT3

In the above SQL, all the inputs INPUT1, INPUT2, INPUT3 are correctly passed in, returns the row. If NOT, I need to find which INPUTx is wrong. I know I could write 3 different Exists Queries to find which one is NOT exists. Is there a better approach? Thanks in advance.

Answer 1

You cannot use the (inner) join, because that would eliminate those records that do not match up. Therefore you would have to use an outer join. Given that, it would likely be faster to use 3 queries anyway. Actually, 4, because you would need 3 to check the 3 values, then another to return the one you want if it exists.

You could do something like this:

SELECT 'Employee Exists' as Result from Employee where EmpId = @INPUT1
union
SELECT 'Department Exists' from Department where DeptId = @INPUT2
union
SELECT 'City Exists' from City where CityId = @INPUT3

to show which ones DO NOT exist, and then return what does with the query you have. And I guess you could combine that into 3 columns instead of 3 rows, but it would still be 3 queries.

Answer 2

The exact logic you want is unclear, but this construct applies the filter as a derived table and you can left join against it. So if d.Name isnull then no match on @INPUT2

DECLARE @INPUT1 BIGINT, @INPUT2 BIGINT, @INPUT3 BIGINT

SELECT
    @INPUT1 AS NotThereIf_eName_IsNull,
    @INPUT2 AS NotThereIf_dName_IsNull,
    @INPUT3 AS NotThereIf_cName_IsNull,
    e.Name, d.Name, c.Name
FROM
    (SELECT @INPUT1 AS EmpID,  @INPUT2 AS DeptID, @INPUT3 AS CityID) dummy
    LEFT JOIN 
    Employee e ON dummy.EmpID = e.EmpID
    LEFT JOIN 
    Department d ON dummy.DeptID = d.DeptID AND e.DeptID = d.DeptID
    LEFT JOIN 
    City c ON dummy.CityID = c.CityID AND e.CityID = c.CityID