Cross checking if value in column 1 is X, value in column 2 should be Y-CodePudding

So I got 2 columns whereby they have different values but are dependent. I want to find those that are not showing the correct values. I really am stumped about what to do.

SELECT *

Let's take those value ends with html should have a value of 3 in the answer column,py should have a value of 4 in the answer column, jpg should have a value of 5 in the answer column and Null should reflect Null in the answer column

webpage	answer
abc.html	3
xyz.py	4
qrs.html	2
syt.jpg	5
NULL	1

Result should show

webpage	answer
qrs.html	2
NULL	1

CodePudding user response：

You can use CASE to check for all those possible combinations. For example:

select t.*,
  case
    when webpage like '%.html' then
      case when answer = 3 then 'VALID' else 'INVALID' end
    when webpage like '%.py' then
      case when answer = 4 then 'VALID' else 'INVALID' end
    when webpage like '%.jpg' then
      case when answer = 5 then 'VALID' else 'INVALID' end
    when webpage is null then
      case when answer is null then 'VALID' else 'INVALID' end
    else 'Unrecognized webpage'
  end as validation
from t

EDIT:

If you only want to show the invalid ones then you can do:

select *
from (
  select t.*,
    case
      when webpage like '%.html' then
        case when answer = 3 then 'VALID' else 'INVALID' end
      when webpage like '%.py' then
        case when answer = 4 then 'VALID' else 'INVALID' end
      when webpage like '%.jpg' then
        case when answer = 5 then 'VALID' else 'INVALID' end
      when webpage is null then
        case when answer is null then 'VALID' else 'INVALID' end
      else 'Unrecognized webpage'
    end as validation
  from t
) x
where validation <> 'VALID'

CodePudding user response：

SELECT
  *
FROM
  your_table
WHERE
     (webpage LIKE '%.html' AND answer <> 3)
  OR (webpage LIKE '%.py'   AND answer <> 4)
  OR (webpage LIKE '%.jpg'  AND answer <> 5)
  OR (webpage IS NULL       AND answer IS NOT NULL)

CodePudding user response：

You can try using CASE to check in WHERE condition:

SELECT * 
  FROM test
   WHERE CASE WHEN webpage LIKE '%html' AND answer=3 THEN 1
              WHEN webpage LIKE '%py' AND answer=4 THEN 1
              WHEN webpage LIKE '%jpg' AND answer=5 THEN 1
              WHEN webpage IS NULL AND answer IS NULL THEN 1
           ELSE 0 END = 0

Fiddle