Page 79 - SQL

SELECT * FROM Employees WHERE DepartmentId = 2 OR ManagerId = 2

Will return:

Id FName LName PhoneNumber ManagerId DepartmentId Salary Hire_date
CreatedDate ModifiedDate
3 Michael Williams 1357911131 1 2 600 12-05-2009 12-05-
2009 NULL
4 Johnathon Smith 1212121212 2 1 500 24-07-2016 24-07-
2016 01-01-2002

Use HAVING to check for multiple conditions in a group

Orders Table

CustomerId ProductId Quantity Price

1 2 5 100

1 3 2 200

1 4 1 500

2 1 4 50

3 5 6 700

To check for customers who have ordered both - ProductID 2 and 3, HAVING can be used

select customerId
from orders
where productID in (2,3)
group by customerId
having count(distinct productID) = 2

Return value:

customerId

1

The query selects only records with the productIDs in questions and with the HAVING clause
checks for groups having 2 productIds and not just one.

Another possibility would be

select customerId
from orders
group by customerId
having sum(case when productID = 2 then 1 else 0 end) > 0

https://riptutorial.com/ 61