rsteckly rsteckly - 1 year ago 70
SQL Question

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 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?

Answer Source

Unless I've misunderstood, I think you want something like this (if you're only interested in customers that have 2 specific issues):

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

FROM Customer c
    JOIN Issues i ON c.CustomerId = i.CustomerId
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download