How to select rows in which several related table values ​​meet the selection criteria?

Question

Based on the following sample table schema

Customer table

CustID
1
2
3

Billing Chart

CustID InvoiceID

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

The goal is to select all clients that have an InvoiceID value of 10 and 20 (not OR). Thus, clients w / CustID = 1 and 2 will be returned in this example.

How would you build a SELECT statement?

Answer 1

Using:

  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 main thing is that the calculation i.invoiceidshould be equal to the number of arguments in the sentence IN.

COUNT(DISTINCT i.invoiceid) , custid invoiceid - , DISTINCT :

  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

Answer 2

, , CustID/InvoiceId. . , , .

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)

Answer 3

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