I have 2 tables. The first table is a list of customers.
The second table is a list of equipment that those customers own with another field with some data on that customer (customer issue). The problem is that for each customer, there may be multiple issues.
I need to do a join on these tables but only return results of customers having two of these issues.
The trouble is, if I do a join with OR, I get results including customers with only one of these issues.
If I do AND, I don't get any results because each row only includes one condition.
How can I do this in T-SQL 2008?
Unless I've misunderstood, I think you want something like this (if you're only interested in customers that have 2 specific issues):
SELECT c.* FROM Customer c INNER JOIN CustomerEquipment e1 ON c.CustomerId = e1.CustomerId AND e1.Issue = 'Issue 1' INNER JOIN CustomerEquipment e2 ON c.CustomerId = e2.CustomerId AND e2.Issue = 'Issue 2'
Or, to find any customers that have multiple issues regardless of type:
;WITH Issues AS ( SELECT CustomerId, COUNT(*) FROM CustomerEquipment GROUP BY CustomerId HAVING COUNT(*) > 1 ) SELECT c.* FROM Customer c JOIN Issues i ON c.CustomerId = i.CustomerId