Search code examples
sqlsql-serversubqueryand-operator

SQL: Using the AND statement with nested queries

I am learning with an online tutorial series and right now we are looking into nested queries. Trying to play around and think of ways I could play with this concept I wanted to try to make combined nested queries, but I am not really sure how and google isn't providing me much luck. Once again it's hard to word things like this. I am using MS SQL

SELECT EmployeeID, FirstName, LastName
FROM SQLTutorial.dbo.EmployeeDemographics 

WHERE EmployeeID
IN (SELECT EmployeeID
FROM SQLTutorial.dbo.EmployeeSalary
WHERE JobTitle = 'DBA')

/*
AND 

WHERE EmployeeID 
IN (SELECT EmployeeID
FROM SQLTutorial.dbo.WareHouseEmployeeDemographics
WHERE Age = 29)
*/

This is what I thought I could do. From the example I know that the uncommented part works. It gets the ID, First Name, and Last name from EDemo IF the ID is in BOTH EDemo AND ESalary AND their job title is DBA.

Well I wanted to limit those results further by only having the results be those who work in the Warehouse as well AND are 29.

I mean if I run the commented code without the uncommented it works like intended as well, but I am not sure why I cannot just combine the two. I am 90% sure it's b/c I can't use the AND statement like this and I have a sneaking feeling I have to nest the two together and make a nest in a nest.

And it will be like a taco inside taco within a Taco Bell that's inside a KFC that's within a mall that's inside your dream! Sorry I couldn't help myself.

Solution

  • Just remove second second "Where" and your query is good to execute. You just need one where clause within which you can have all your condition combined with and, or etc..

    SELECT EmployeeID, FirstName, LastName
FROM SQLTutorial.dbo.EmployeeDemographics 

WHERE EmployeeID
IN (SELECT EmployeeID
FROM SQLTutorial.dbo.EmployeeSalary
WHERE JobTitle = 'DBA')

AND EmployeeID 
IN (SELECT EmployeeID
FROM SQLTutorial.dbo.WareHouseEmployeeDemographics
WHERE Age = 29)

    If you want all the employees whose ID is in BOTH EDemo AND ESalary AND their job title is DBA but or who work in the Warehouse AND are 29.

     SELECT EmployeeID, FirstName, LastName
    FROM SQLTutorial.dbo.EmployeeDemographics 
    
    WHERE EmployeeID
    IN (SELECT EmployeeID
    FROM SQLTutorial.dbo.EmployeeSalary
    WHERE JobTitle = 'DBA')
    
    or EmployeeID 
    IN (SELECT EmployeeID
    FROM SQLTutorial.dbo.WareHouseEmployeeDemographics
    WHERE Age = 29)