Multiple conditions on multiple columns-CodePudding

I have table that looks like this

   WO | PS | C
----------------
   12 | 1  | a
   12 | 2  | b
   12 | 2  | b
   12 | 2  | c
   13 | 1  | a

I want to find values from WO column where PS has value 1 and C value a AND PS has value 2 and C has value b. So on one column I need to have multiple conditions and I need to find it within WO column. If there is no value that matches two four conditions I don't want to have column WO included.

I tried using condition:

WHERE PS = 1 AND C = a AND PS = 2 AND C = b

but it does not work and does not have connection to WO column as mentioned above.

Edit:

I need to find WO which has (PS = 1 AND C = a) and at the same time it also has rows where (PS = 2 and C = b).

The result should be:

   WO | PS | C
----------------
   12 | 1  | a
   12 | 2  | b
   12 | 2  | b

If either of rows: (PS = 1 and C = a) or (PS = 2 and C = b) does not exist then nothing should be returned.

CodePudding user response：

WHERE (PS = 1 AND C = a) or (PS = 2 AND C = b)

try this condition

CodePudding user response：

As I understand this, you need two IN clauses or two EXIST clauses, something like this:

SELECT DISTINCT wo, ps, c
FROM yourtable
WHERE wo IN 
  (SELECT wo FROM yourtable WHERE ps = 1 and c = 'a')
AND wo IN 
  (SELECT wo FROM yourtable WHERE ps = 2 and c = 'b');

This will produce this outcome:

   WO | PS | C
----------------
   12 | 1  | a
   12 | 2  | b
   12 | 2  | c

Please note that in the last row of the result, the column C has value c instead of b as you have shown in your question. I guess this was your mistake when creating the sample outcome?

If I understand your question incorrect, please let me know and explain what's wrong, then I would review it.

Edit: To create the same result as shown in your question, this query would do:

SELECT wo, ps, c
FROM yourtable
WHERE ps IN (1,2) AND c IN ('a','b')
AND wo IN 
  (SELECT wo FROM yourtable WHERE ps = 1 and c = 'a')
AND wo IN 
  (SELECT wo FROM yourtable WHERE ps = 2 and c = 'b');

But I really don't believe this is what you were looking for ;)

Try out: db<>fiddle

CodePudding user response：

I think you can make use of an exists criteria here to filter your rows correctly, I would like to see a wider sample data set to be sure though.

select *  
from t
where ps in (1,2) and C in ('a','b')
and exists (
  select * from t t2 where t2.WO = t.WO
  and t2.PS != t.PS and t2.C != t.C
);

CodePudding user response：

Let's chat about demo data. You provided some useful data that helps us see what your problem is, but no DDL. If you provide your demo data similar to this, it makes it easier for us to understand the issue:

DECLARE @table TABLE (WO INT, PS INT, C NVARCHAR(10))
INSERT INTO @table (WO, PS, C) VALUES
(12, 1, 'a'), (12, 2, 'b'),
(12, 2, 'b'), (12, 2, 'c'),
(13, 1, 'a')

Now on to your question. It looks to me like you just need a composite conditions and that one of them needs to evaluate to fully true. Consider this:

SELECT *
  FROM @table
 WHERE (
            PS = 1 
        AND C = 'a'
       )
    OR (
            PS = 2 
        AND C = 'b'
       )

The predicates wrapped in the parens are evaluated as a whole in the WHERE clause. If one of the predicates is false, the whole thing is. If either composite evaluates to true, we return the row.

WO  PS  C
---------
12  1   a
12  2   b
12  2   b
13  1   a

This result set does include WO 13, as by your definition it should be there. I don't know if there are additional things you wanted to evaluate which may exclude it, but it does have a PS of 1 and a C of a.

CodePudding user response：

Just to throw in one more solution, you can do this with a single reference to your table, but this may not necessarily mean that it is more efficient. The first part is to filter based on the combinations you want:

DECLARE @T TABLE (WO INT, PS INT, C CHAR(1))
INSERT @T (WO, PS, C) 
VALUES (12, 1, 'a'), (12, 2, 'b'), (12, 2, 'b'), (12, 2, 'c'), (13, 1, 'a');

SELECT  *
FROM    @T AS t
WHERE   (t.PS = 1 AND t.C = 'a') 
OR      (t.PS = 2 AND t.C = 'B');

WO	PS	C
12	1	a
12	2	b
12	2	b
13	1	a

But you want to exclude WO 13 because this doesn't have both combinations, so what we ideally need is a count distinct of WS and C to find those with a distinct count of 2. You can't do COUNT(DISTINCT ..) in a windowed function directly, but you can do this indirectly with DENSE_RANK():

DECLARE @T TABLE (WO INT, PS INT, C CHAR(1))
INSERT INTO @T (WO, PS, C) 
VALUES (12, 1, 'a'), (12, 2, 'b'), (12, 2, 'b'), (12, 2, 'c'), (13, 1, 'a');

SELECT  *,
        CntDistinct = DENSE_RANK() OVER(PARTITION BY t.WO ORDER BY t.PS, t.C)   
                        DENSE_RANK() OVER(PARTITION BY t.WO ORDER BY t.PS DESC, t.C DESC) - 1
FROM    @T AS t
WHERE   (t.PS = 1 AND t.C = 'a') 
OR      (t.PS = 2 AND t.C = 'B');

Which gives:

WO	PS	C	CntDistinct
12	1	a	2
12	2	b	2
12	2	b	2
13	1	a	1

You can then put this in a subquery and chose only the rows with a count of 2:

DECLARE @T TABLE (WO INT, PS INT, C CHAR(1))
INSERT INTO @T (WO, PS, C) 
VALUES (12, 1, 'a'), (12, 2, 'b'), (12, 2, 'b'), (12, 2, 'c'), (13, 1, 'a');

SELECT  t.WO, t.PS, t.C
FROM    (   SELECT  t.*,
                    CntDistinct = DENSE_RANK() OVER(PARTITION BY t.WO ORDER BY t.PS, t.C)   
                                    DENSE_RANK() OVER(PARTITION BY t.WO ORDER BY t.PS DESC, t.C DESC) - 1
            FROM    @T AS t
            WHERE   (t.PS = 1 AND t.C = 'a') 
            OR      (t.PS = 2 AND t.C = 'B')
        ) AS t
WHERE   t.CntDistinct = 2;

Finally, if the combinations are likely change, or are a lot more than 2, you may find building a table of the combinations you are looking for a more maintainable solution:

DECLARE @T TABLE (WO INT, PS INT, C CHAR(1))
INSERT INTO @T (WO, PS, C) 
VALUES (12, 1, 'a'), (12, 2, 'b'), (12, 2, 'b'), (12, 2, 'c'), (13, 1, 'a');

DECLARE @Combinations TABLE (PS INT, C CHAR(1), PRIMARY KEY (PS, C));
INSERT @Combinations(PS, C)
VALUES (1, 'a'), (2, 'b');

SELECT  t.WO, t.PS, t.C
FROM    (   SELECT  t.*,
                    CntDistinct = DENSE_RANK() OVER(PARTITION BY t.WO ORDER BY t.PS, t.C)   
                                    DENSE_RANK() OVER(PARTITION BY t.WO ORDER BY t.PS DESC, t.C DESC) - 1
            FROM    @T AS t
                    INNER JOIN @Combinations AS c
                        ON c.PS = t.PS
                        AND c.C = t.C
        ) AS t
WHERE   t.CntDistinct = 2;