How to Exclude Records with Certain Values in SQL Select

How to exclude records with certain values in sql select

One way:

SELECT DISTINCT sc.StoreId
FROM StoreClients sc
WHERE NOT EXISTS(
    SELECT * FROM StoreClients sc2 
    WHERE sc2.StoreId = sc.StoreId AND sc2.ClientId = 5)

How to exclude records with certain values in sql

Select col1,col2
From table
Where col1 not in (Select col1 from table where col2 not in (1,20))

Exclude records with specific value '*' SQL SELECT

This can be done in so many ways..

Using NOT LIKE

SELECT prdcod
FROM prdtbl
WHERE prodcod NOT LIKE '*%'

Using LEFT/SUBSTRING

SELECT prdcod
FROM prdtbl
WHERE LEFT(prodcod,1) <> '*' -- SUBSTRING(prodcod from 1 for 1) <> '*'

Note : Not Like is preferred approach if you have a Index on prodcod column

Exclude Certain Records with Certain Values SQL Select

You could use EXISTS:

SELECT StoreId
FROM sc
WHERE NOT EXISTS (
  SELECT 1
  FROM StoreClients cl
  WHERE sc.StoreId = cl.StoreId
  AND cl.ClientId = 5
  )

Make sure to create an index on StoreId column. Your query could also benefit from an index on ClientId.

Exclude values from results if a column contains a specific value

You can use exists:

select t.*
from t
where (t.id is null and t.[key] is null) or
      (not exists (select 1
                  from t t2
                  where t2.id = t.id and t2.[key] is not null
                 ) and
       t.[key] is null
      );

You can also use window functions:

select t.*
from (select t.*,
             max(key) over (partition by id) as max_key
      from t
     ) t
where max_key is null or (id is null and [key] is null);

Here is a db<>fiddle.

Your rules are not quite as you state them. The rules appear to be:

• id is NULL and key is null; or
• There is no non-NULL key on a non-NULL id.

How to exclude records from SQL query?

you could use decode function

select t 
from tableA t, tableA t2 
where t.validAt = :validAt1 and t2.validAt = :validAt2 
and t.uniqueId = t2.uniqueId 
and nvl(t.code, 'xNVLx') != nvl(decode(t2.code,'00','xNVLx','01','xNVLx',t2.code), 'xNVLx');

How do I exclude rows in SQL where string does not contain two words

You want OR

.. not (name like '%ab%' and name like '%test%')

this is the same as

select
name
from example
where name not like '%ab%' OR  name not like '%test%

SQL - Exclude rows from SELECT statement if a certain column combination exists in that row

Since a row can only match one of those conditions at one time, you should be using OR:

SELECT COLUMN1, COLUMN2, COLUMN3
FROM YourTable
WHERE NOT (    
    ( COLUMN2 = 'A' AND COLUMN3 = 'B' )
     OR 
    ( COLUMN2= 'B' AND COLUMN3 = 'C' )
)

db<>fiddle here

sql select query excluding certain values

Try:

SELECT *
FROM tablee
WHERE ColumnA NOT IN (
  SELECT columnA FROM tablee
  WHERE columnB = 1
);

or

SELECT *
FROM tablee t1
WHERE NOT EXISTS(
  SELECT 1 FROM tablee t2
  WHERE t2.columnB = 1 and t1.columnA = t2.columnA
);

Exclude records with certain values in Qubole

Use analytic functions.

Demo:

with your_table as (--use your table instead of this sample
select stack(6,
1,'GOOD','GOOD',
2,'BAD','BAD'  ,
2,'GOOD','BAD' ,
3,'GOOD','BAD' ,
4,'BAD','GOOD' ,
4,'GOOD','BAD') as (ID,Recommendation,Decision)
)

select ID,Recommendation,Decision
from
(
select d.*, 
       count(*) over(partition by id) as cnt,
       count(case when Recommendation  = 'GOOD' then 1 end) over(partition by id) cnt_Recommendation_good, 
       count(case when Decision  = 'BAD' then 1 end) over(partition by id) cnt_Decision_BAD
  from 
  your_table d
) s
where cnt_Recommendation_good=cnt
  and cnt_Decision_BAD = cnt

Result:

id  recommendation  decision
3   GOOD            BAD

---

Related Topics