Sql-server – See whether a groupped item have more than one value in a database column

sql server

I am working on SQL database where there are several thousands of lines in one table. This table (product_lines) looks like this: 

sku price   Discount    FinalPrice
1   29      5           24
1   29      5           24
2   30      5           25
2   30      0           30
3   15      0           15
3   15      0           15

Each line contains SKU's information when used in a receipt. SKUs may repeat within the rows (eg sku:2) but discount vary each time. I am trying to collect the items that exist both with and without a value in the column discount — to collect the SKUs that have sold with and without discount. The result I am looking for should look like:

  sku price   Discount    FinalPrice
  2   30      5           25
  2   30      0           30

How can I do this?

Best Answer

You can either use an IN clause with an intersect

select *
from product_lines
where sku in (select sku 
              from product_lines
              where discount > 0
              intersect
              select sku
              from product_lines
              where discount = 0);

Using exists might be faster with SQL Server:

select *
from product_lines p1
where exists (select *
              from product_lines p2
              where p2.discount > 0
               and p2.sku = p1.sku)
  and exists (select * 
              from product_lines p3
              where p3.sku = p1.sku
                and p3.discount = 0);
Related Question