Need Help In Sql Filter Query

Question

I Have 3 Dropdwon Branch,department,level And i Want to filter record according to these 3 parameters all parameters is nullable    my query is   SELECT e.EmployeeID,e.LevelID,e.EmployeeCode,e.EmailID,e.MobileNo,e.BranchID,e.DepartmentID,e.EmployeeName, b.BranchName,d.DepartmentName FROM EMPLOYEE e LEFT OUTER JOIN Department d ON d.DepartmentID=e.DepartmentID LEFT OUTER JOIN Branch b ON b.BranchID=e.BranchID WHERE e.IsDeleted<>1 AND @BranchID IS Null or e.BranchID=@BranchID AND @DepartmentID IS NULL OR e.DepartmentID=@DepartmentID AND @LevelID IS NULL OR e.LevelID=@LevelID       Pls Help Me

Mohit Kala · Answer

Display Page When i select Branch Mundra it displays data but when i select department of mundra branch it will shows other branch data also

Ramachandran M · Answer

Hi Mohit,   Try this   SELECT e.EmployeeID,e.LevelID,e.EmployeeCode,e.EmailID,e.MobileNo,e.BranchID,e.DepartmentID,e.EmployeeName,
b.BranchName,d.DepartmentName FROM EMPLOYEE e
LEFT OUTER JOIN Department d ON d.DepartmentID=e.DepartmentID
LEFT OUTER JOIN Branch b ON b.BranchID=e.BranchID
WHERE e.IsDeleted<>1 AND   e.BranchID = CASE WHEN @BranchID IS Null THEN  e.BranchID ELSE e.BranchID=@BranchID END
AND  e.DepartmentID = CASE WHEN @DepartmentID IS Null THEN  e.DepartmentID ELSE e.DepartmentID=@DepartmentID END
AND e.LevelID = CASE WHEN @LevelID IS Null THEN  e.LevelID ELSE e.LevelID=@LevelID END

Siddharth Kushwah · Answer

please add relation also in this query like that  also add relation with  b.BranchID=d. BranchID (relation with branch's branch id and department's branch id) And i am sure it will give right data.
SELECT e.EmployeeID,e.LevelID,e.EmployeeCode,e.EmailID,e.MobileNo,e.BranchID,e.DepartmentID,e.EmployeeName, b.BranchName,d.DepartmentName FROM EMPLOYEE e LEFT OUTER JOIN Branch b ON b.BranchID=e.BranchID LEFT OUTER JOIN Department d ON d.DepartmentID=e.DepartmentID   and b.BranchID=d.BranchID WHERE e.IsDeleted<>1 AND @BranchID IS Null or e.BranchID=@BranchID AND @DepartmentID IS NULL OR e.DepartmentID=@DepartmentID AND @LevelID IS NULL OR e.LevelID=@LevelID

Ramesh Palanivel · Answer

Hi Mohit,   what is the result you are getting? Are you getting the expected result or not?   Please share some sample data to us, it will help us to provide sql query for your scenario. :)