How to select rows where multiple joined table val

问题:

Given the following sample table schema

Customer Table

CustID
1
2
3

Invoice Table

CustID InvoiceID

1       10
1       20
1       30
2       10
2       20
3       10
3       30

The objective is to select all customers who have an InvoiceID value of 10 and 20 (not OR). So, in this example customers w/ CustID=1 and 2 would be returned.

How would you construct the SELECT statement?

回答1:

Use:

  SELECT c.custid
    FROM CUSTOMER c
    JOIN INVOICE i ON i.custid = c.custid
   WHERE i.invoiceid IN (10, 20)
GROUP BY c.custid
  HAVING COUNT(DISTINCT i.invoiceid) = 2

The key thing is that the counting of i.invoiceid needs to equal the number of arguments in the IN clause.

The use of COUNT(DISTINCT i.invoiceid) is in case there isn't a unique constraint on the combination of custid and invoiceid -- if there's no chance of duplicates you can omit the DISTINCT from the query:

  SELECT c.custid
    FROM CUSTOMER c
    JOIN INVOICE i ON i.custid = c.custid
   WHERE i.invoiceid IN (10, 20)
GROUP BY c.custid
  HAVING COUNT(i.invoiceid) = 2

回答2:

The Group By answers will work unless it is possible for there to be multiples of CustID/InvoiceId in the Invoice table. Then you might get some unexpected results. I like the answer below a little better because it mirrors more closely the logic as you are describing it.

Select CustID
From Customer
Where
  Exists (Select 1 from Invoice Where CustID=Customer.CustID and InvoiceID=10)
and
  Exists (Select 1 from Invoice Where CustID=Customer.CustID and InvoiceID=20)

回答3:

select CustID
    from InvoiceTable
    where InvoiceID in (10,20)
    group by CustID
    having COUNT(distinct InvoiceID) = 2