Select number of times 2 specific values show up for one record id I'm not sure my title above makes sense, so I'll try to clarify.  I'm using SQL Server.

I have a table called Orders that looks like this (truncated):

Order_Number | SKU
           1 | abc
           1 | def
           1 | ghi
           2 | abc
           2 | ghi
           3 | def
           4 | abc
           4 | def
           4 | ghi
           5 | abc


I need to find all Order Numbers that include SKUs abc and ghi.  So if I ran the query on the above table, it would return:

Order_Number
           1
           2
           4


If the Order Number only includes abc or ghi, not both, I don't want to return the Order Number.  If it includes extra SKUs, such as def, that is OK, I still want to return the record.  I don't need to see all the SKUs in the output, just the Order Number.  Does that make sense?

Thanks in advance!
You can do this in two different ways:

1) Use EXISTS 

SELECT
    Order_Number
FROM
    (-- Get the list of Order_Number
    SELECT DISTINCT
        Order_Number
    FROM
        Orders
    ) AS onr
WHERE
    -- Check that there is an 'abc' associated
    EXISTS
    (
    SELECT *
    FROM Orders os1
    WHERE os1.Order_Number = onr.Order_Number and os1.SKU = 'abc'
    )
    AND
    -- and also Check that there is an 'ghi' associated
    EXISTS
    (
    SELECT *
    FROM Orders os2
    WHERE os2.Order_Number = onr.Order_Number and os2.SKU = 'ghi'
  )
ORDER BY
    onr.Order_Number
GO



| Order_Number |
| -----------: |
|            1 |
|            2 |
|            4 |


2) Substitute the EXISTS by equivalent JOINS with join condition including the desired value of SKU:

SELECT
    onr.Order_Number
FROM
    (SELECT DISTINCT 
        Order_Number 
    FROM 
        Orders
    ) AS onr
    JOIN Orders AS sk1 ON sk1.Order_Number = onr.Order_Number AND sk1.SKU = 'abc'
    JOIN Orders AS sk2 ON sk2.Order_Number = onr.Order_Number AND sk2.SKU = 'ghi'
ORDER BY
    onr.Order_Number    
GO


You get exactly the same result. Which version you use depends on which one you find clearer to understand. If your tables are really big, test whether one gives a better execution plan.

You can check all the statments at dbfiddle here