Menu
Let's say I have 2 tables:
If I wanted to list employees that belong to locations 1, 2, or 3 I would write a query similar to:
SELECT DISTINCT e.id, e.name
FROM Employee e
JOIN EmpLocXref x ON x.employee_id = e.id
WHERE x.location_id IN (1, 2, 3)
;
Example.
But what if I wanted to list employees that only belong to locations 1, 2, and 3? I realize I could write something similar to:
SELECT e.id, e.name
FROM Employee e
JOIN EmpLocXref x ON x.employee_id = e.id
WHERE x.location_id IN (1, 2, 3)
GROUP BY e.id, e.name
HAVING COUNT(*) = 3
;
Example
Is there a better way to do this that doesn't include dynamic SQL?
EDIT: Fixed derp moment on 2nd query. Added Fiddler examples.
697
or logic operator any of the Boolean symbols or functions, as AND, OR, and NOT, denoting a Boolean operation; Boolean operator.
The logical AND operator ( && ) returns true if both operands are true and returns false otherwise. The operands are implicitly converted to type bool before evaluation, and the result is of type bool . Logical AND has left-to-right associativity.
The && and || Operators in JavaScript. If applied to boolean values, the && operator only returns true when both of its operands are true (and false in all other cases), while the || operator only returns false when both of its operands are false (and true in all other cases).
&& (Logical AND) This operator returns true if all relational statements combined with && are true, else it returns false.
The above actually wouldn't work, because you'd never have a record in EmpLocXref where the location_id is 1 and 2 and 3 all at the same time. You'd get zero results. Try this instead:
SELECT DISTINCT e.*
FROM Employee e
INNER JOIN EmpLocXref x1 ON e.ID = x1.EmployeeID AND x1.Location_id = 1
INNER JOIN EmpLocXref x2 ON e.ID = x2.EmployeeID AND x2.Location_id = 2
INNER JOIN EmpLocXref x3 ON e.ID = x3.EmployeeID AND x3.Location_id = 3
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
