Is there a better way to specify conditions in the WHERE statement?-CodePudding

This is a pretty simple question, however I’m still new to SQL and haven’t been able to figure this out. I’m currently trying to indicate in my query that I want to see results of employees who are working remotely AND are in IT, Analysts, and Salesmen.

Here is my query right now (shortened):

SELECT a.EmpName, b.EmpNo, c.EmpTitle
FROM Table a
  JOIN Table b ON a.EmpID = b.EmpID
  JOIN Table c ON a.EmpID = c.EmpID
  JOIN Table d ON a.EmpID = d.EmpID
  JOIN Table e ON a.EmpID = e.EmpID
WHERE d.EmpRemoteFlag = ‘Y’
  AND e.EmpType IN (‘IT’, ‘Analyst’, ‘Salesmen’)

With the way this query is, the EmpRemoteFlag = ‘Y’ condition is being ignored while the EmpType condition is not.

I’ve tried adding parameters in the query and I’ve also tried adding parentheses around both conditions in the WHERE clause and neither have worked; one is still getting ignored. Any help is greatly appreciated!

CodePudding user response：

Query looks perfect, maybe when you shortened id you removed sth important, like ' OR ' in where condition?

CodePudding user response：

With the way this query is, the EmpRemoteFlag = ‘Y’ condition is being ignored while the EmpType condition is not.

No. Why do you say that? You define both conditions and say "AND" - that results in both having to be true. The EmpRemoteFlag is NOT ignored.

Do some testing.

And to answer the tile: No. THis is the way you define them. You COULD put some of the conditions into the JOIN's, but the general approach os that the conditions there are ONLY for defining the Join, while the WHERE clause is to filter them. This is perfectly fine as a way to do it.

CodePudding user response：

Query seems good there is no issue in where condition, issue might be in selecting the EmpRemoteFlag column from the right table or some join condition issues. If you can tell what this 4 tables are and what exactly you want it might help. Dont focus on where condition its fine.