Aggregation depends from value [closed]

I have a sample table

ID	Invoice_ID	docType
1	100	email
2	100	sms
3	200	email
4	200	email
5	300	sms

and I have to get results with only rows that have invoices with email and sms docType. For this example its only invoice_id 100

Answer

One approach is to query those type and count the distinct number of different types that appear:

SELECT   invoice_id
FROM     invoices
WHERE    doctype IN ('sms', 'email')
GROUP BY invoice_id
HAVING   COUNT(DISTINCT doctype) = 2