Exclude Records Based on Criteria-CodePudding

I need to exclude the record:

If a customer bought "Shoe" and "Hat" on the same day, exclude the record with "Hat".
If a customer bought "Shoe" and "Cloth" on the same day, exclude the record with "Cloth".

Here is the sample data:

create table tbl
(
  Customer  varchar(5),
  Day date,
  Product  varchar(5),
  Cost  decimal(10,2)
);

insert into tbl values 
('A',       '1/1/2019',      'Shoe',        10.00),
('B',       '2/4/2021',      'Hat',          10.00),
('B',       '5/8/2018',      'Shoe',         10.00),
('C',       '6/6/2020',      'Hat',         10.00),
('C',       '11/9/2021',      'Cloth',       10.00),
('E',       '7/2/2020',      'Hat',        10.00),
('A',       '7/5/2022',      'Hat',       10.00),
('C',       '6/6/2020',      'Shoe',        10.00),
('A',       '1/1/2019',      'Cloth',        10.00),
('E',       '7/2/2020',      'Shoe',       10.00);

select *
from   tbl
group by Customer, Day, Product, Cost
order by Customer, Day

Here are the expected results:

CodePudding user response：

Self join to your shoe criteria and make sure it is not there, like this:

select tbl.Customer, tbl.Day, tbl.Product, tbl.Cost
from   tbl
left join tbl as shoe on tbl.customer = shoe.customer and
                    tbl.day = shoe.day and
                    shoe.product = 'Shoe'
where not (tbl.product in ('Hat','Cloth') and coalesce(shoe.product,'') = 'Shoe')
group by tbl.Customer, tbl.Day, tbl.Product, tbl.Cost
order by tbl.Customer, tbl.Day

CodePudding user response：

SELECT t1_c    customer,
       t1_d    date_,
       t1_p    product,
       t1_cost cost
  FROM (SELECT t2.product  t2_p,
               t2.day      t2_d,
               t1.product  t1_p,
               t1.day      t1_d,
               t1.customer t1_c,
               t1.cost     t1_cost
          FROM tbl t1
          LEFT JOIN tbl t2 ON t2.day = t1.day
                          AND t2.customer = t1.customer
                          AND t2.product = 'Shoe') t
 WHERE (t2_p IS NOT NULL
   AND t1_p = 'Shoe')
    OR t2_p IS NULL

CodePudding user response：

You want all the rows with 'Shoe' and only those rows with other products if at the same day the customer hasn't bought a 'Shoe'.
Use NOT EXISTS:

SELECT t1.*
FROM tbl t1
WHERE t1.Product = 'Shoe'
   OR NOT EXISTS (
     SELECT *
     FROM tbl t2
     WHERE t2.Customer = t1.Customer AND t2.Day = t1.Day AND t2.Product = 'Shoe'
   );

Or, if there are more products than the ones included in the sample data:

SELECT t1.*
FROM tbl t1
WHERE t1.Product NOT IN ('Hat', 'Cloth')
   OR NOT EXISTS (
     SELECT *
     FROM tbl t2
     WHERE t2.Customer = t1.Customer AND t2.Day = t1.Day AND t2.Product = 'Shoe'
   );

See the demo.