如何在第二个已联接表中执行具有多个条件的联接?

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.

问题是，如果我加入OR，我只会得到其中一个问题的客户的结果。

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?

如何在T-SQL 2008中做到这一点?

3 个解决方案

#1

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

#2

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

#3

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

您可以使用子查询代替join:

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

or whatever value you want

或者你想要的任何价值

#1