SQL Server sql condition multiple condition when column a =! column b BUT with exception

bxgwgixi  于 2024-01-05  发布在  其他
关注(0)|答案(2)|浏览(104)

How to write query for such case
| Column A | Column B |
| ------------ | ------------ |
| Cell ABC | 1 |
| Cell oo | bb |
| Cell BCD | 2 |
| Cell bb | oo |

I need to meet conditions :

Show me only results of Column A and B when they differs BUT with exception when
Column A = 'oo' and Column B = 'bb' or Column A = 'bb' and Column B = 'oo' i don't want them in my results (they have exception)

Based on this example i expect to see in results only
| | |
| ------------ | ------------ |
| Cell ABC | 1 |
| Cell BCD | 2 |

I tried query

  1. Select * from Table where Column A ! = Column B

but I am stuck with part to exclude when columns have particular values.

b4wnujal

b4wnujal1#

You can try those conditions :

  1. SELECT *
  2. FROM mytable
  3. WHERE (
  4. columnA <> columnB
  5. AND NOT (columnA = 'bb' and columnB = 'oo'
  6. or columnA = 'oo' and columnB = 'bb')
  7. ) OR columnA IS NULL OR columnB IS NULL;

Which, for the sample data:

  1. create table mytable (
  2. ColumnA varchar(20),
  3. ColumnB varchar(20)
  4. );
  5. insert into mytable values
  6. ('ABC', '1'),
  7. ('oo', 'bb'),
  8. ('oo', NULL),
  9. (NULL, 'bb'),
  10. (NULL, NULL),
  11. ('BCD', '2'),
  12. ('bb', 'oo');

Outputs :

  1. ColumnA ColumnB
  2. ABC 1
  3. oo null
  4. null bb
  5. null null
  6. BCD 2

Demo here

展开查看全部
mrphzbgm

mrphzbgm2#

You can use the condition to exclude the records and then negate them with NOT as follows:

  1. SELECT *
  2. FROM YOUR_TABLE
  3. WHERE COLUMN_A <> COLUMN_B
  4. AND NOT ((COLUMN_A = 'oo' AND COLUMN_B = 'bb')
  5. OR (COLUMN_A = 'bb' AND COLUMN_B = 'oo'))

相关问题