In SQL filter out one column records based on different column condition-CodePudding

I have 2 columns Source_System, SBG_NAME. I want to filter out Source system Microsiga for SBG NAME PMT

Data set is like this:

Source_System   SBG_Name
CIP300            PMT
Microsiga         PMT
CIP900            HBT
Microsiga         HBT

oUTPUT SHOULD BE

Source_System   SBG_Name
CIP300            PMT
CIP900            HBT
Microsiga         HBT

CodePudding user response：

Or flipping SChnurres answer, and just not selection what you don't want

SELECT
    Source_System
    ,SBG_Name 
FROM VALUES 
  ('CIP300','PMT'),
  ('Microsiga','PMT'),
  ('CIP900','HBT'),
  ('Microsiga','HBT')
  v(Source_System,  SBG_Name)
WHERE NOT(Source_System = 'Microsiga' and SBG_Name = 'PMT');

gives:

SOURCE_SYSTEM	SBG_NAME
CIP300	PMT
CIP900	HBT
Microsiga	HBT

CodePudding user response：

SELECT
"Source_System"
,"SBG_Name"
FROM table

MINUS

SELECT
"Source_System"
,"SBG_Name"
FROM table
WHERE "Source_System" = 'Microsiga' and "SBG_Name" = 'PMT'

You could get the result with something like this. This shows all records, except the ones where Source_System is Microsiga and SBG_Name is PMT.

Alternatively this yields the same result and is even more compact:

SELECT
"Source_System"
,"SBG_Name"
FROM table
WHERE  NOT ("Source_System" = 'Microsiga' and "SBG_Name" = 'PMT')