How to do a join with multiple conditions in the second joined table?

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 inc开发者_StackOverflow中文版luding 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

SELECT * 
FROM customers as c
LEFT JOIN equipment as e
ON c.customer_id = e.customer_id  --> what you are joining on
WHERE (
          SELECT COUNT(*) 
          FROM equipment as e2 
          WHERE e2.customer.id = c.customer_id
      ) > 1

You can do it with a sub query instead of Joins:

select * from Customer C where (select Count(*) from Issue I where I.CustomerID = C.CustomerID) < 2 

or whatever value you want