I have a table below:

UPC	Article_#	MS_BC	MS_PRA	MS_ON	MS_QUE	MS_ATL
123	A123	A	B	A	B	A
124	A123	A	A	B	B	B
125	A124	C	A	B	B	B
126	A124	A	A	B	B	B

I want to keep rows of Article_# if all of the MS values are in A or B only. If any of the rows contain C then I need to remove all rows for the same Article_#. In example below, because MS_BC = 'C' for Article# 124, the entire article_# (2 rows) must be removed. This is what I need to see:

UPC	Article_#	MS_BC	MS_PRA	MS_ON	MS_QUE	MS_ATL
123	A123	A	B	A	B	A
124	A123	A	A	B	B	B

How do I do that?

CodePudding user response：

One more way to handle it.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (UPC INT PRIMARY KEY, Article_# VARCHAR(10), MS_BC CHAR(1),MS_PRA CHAR(1), MS_ON CHAR(1), MS_QUE CHAR(1), MS_ATL CHAR(1));
INSERT INTO @tbl (UPC, Article_#, MS_BC, MS_PRA, MS_ON, MS_QUE, MS_ATL) VALUES
('123', 'A123', 'A', 'B', 'A', 'B', 'A'),
('124', 'A123', 'A', 'A', 'B', 'B', 'B'),
('125', 'A124', 'C', 'A', 'B', 'B', 'B'),
('126', 'A124', 'A', 'A', 'B', 'B', 'B');
-- DDL and sample data population, end

;WITH rs AS
(
    SELECT Article_# 
    FROM @tbl
    CROSS APPLY (VALUES 
        (MS_BC), (MS_PRA), (MS_ON), 
        (MS_QUE), (MS_ATL)) AS t(c)
    WHERE c NOT IN ('A', 'B')
)
SELECT * 
FROM @tbl
WHERE  Article_# NOT IN (SELECT Article_# FROM rs);

Output

 ----- ----------- ------- -------- ------- -------- -------- 
| UPC | Article_# | MS_BC | MS_PRA | MS_ON | MS_QUE | MS_ATL |
 ----- ----------- ------- -------- ------- -------- -------- 
| 123 | A123      | A     | B      | A     | B      | A      |
| 124 | A123      | A     | A      | B     | B      | B      |
 ----- ----------- ------- -------- ------- -------- -------- 

CodePudding user response：

If your table has many rows then using LIKE as suggested in other answers will be slow.

Something like this should perform well:

SELECT *
FROM table_name
WHERE Article_# IN (
    SELECT Article_#
    FROM table_name
    WHERE 
        MS_BC     NOT IN ('A', 'B')
        OR MS_PRA NOT IN ('A', 'B')
        OR MS_ON  NOT IN ('A', 'B')
        OR MS_QUE NOT IN ('A', 'B')
        OR MS_ATL NOT IN ('A', 'B')
);

CodePudding user response：

Equality is symmetrical in nature, so you could do

select * 
from t
where article_# not in (select article_# 
                        from t 
                        where 'C' in (ms_bc, ms_pra, ms_on, ms_que, ms_atl));

Be careful with not in though, if you have nulls

CodePudding user response：

I'd go with using not exists:

select
*
from
table1 t
where not exists (select article from table1 where ms_bc = 'C' and t.article = table1.article)

Fiddle

CodePudding user response：

Here is one way, using LIKE and a concatenation trick:

WITH cte AS (
    SELECT *, COUNT(CASE WHEN MS_BC   MS_PRA   MS_ON   MS_QUE   MS_ATL
                              LIKE '%C%' THEN 1 END)
                  OVER (PARTITION BY [Article_#]) AS cnt
    FROM yourTable
)

SELECT UPC, [Article_#], MS_BC, MS_PRA, MS_ON, MS_QUE, MS_ATL
FROM cte
WHERE cnt = 0;

We form a string of 5 characters from the MS columns and then check that a C character be present for any given article.