How to filter the result of an SQL query by filtering one column using data from another

user_13044455 0
Viewed 359

I have the following structure on a table named LINEFAC:

OPERATION        CUSTOMER
4C201900         720
3V191025         650
3V191021         720
3V191021         721
3V191021         720
3V191018         520
3V191017         198
3V191016         789
3V191021         798
3V191014         720

And I need to show the results only when, on the same operation number (first column), the customer (second column) are 720 and 721 at the same time.

So the query result for this example must be:

OPERATION        CUSTOMER
3V191021         720
3V191021         721
3V191021         720

I tried several combinations with the WHERE, ROW_NUMBER and many more statements without retrieving the desired data.

Thanks in advance.

sql-server sql
2 Answers

You want exists :

select t.*
from LINEFAC lf
where exists (select 1 
              from LINEFAC lf1 
              where lf1.operation = lf.operation and 
                    lf1.customer <> lf.customer
             );
user_55294450

If it is sufficient to get operations, then I recommend group by and having:

select operation
from t
where customer in (720, 721)
group by operation
having count(distinct customer) = 2;

If you want the original rows, then I recommend exists:

select t.*
from t
where t.customer in (720, 721) and
      exists (select 1
              from t t2
              where t2.operation = t.operation and
                    t2.customer in (720, 721) and
                    t2.customer <> t.customer
             );
user_11440350
Related