Sql Query Exclude Records

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 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%

Exclude records based off particular string text

Well, this is a little hard to do with like, but one method is:

select *
from #temp
where not (Address like 'Room 1[0-9]%' or
           Address like 'Room [2-9]%'
          )

That is, the address does not have Room with 1 followed by a digit or Room followed by 2-9.

SQL Server - Exclude records from query result set based on condition

I think you want a having clause; and you can simplify the computation logic with AVG():

SELECT S.[DepartmentId],
    AVG(CASE WHEN [IsFTEStudent] = 1 THEN 1.0 ELSE 0 END) AS [AverageOfFTEStudents]
FROM dbo.[Student] S
INNER JOIN [dbo].User U ON ON S.[StudentId] = U.[UserId]
INNER JOIN dbo.[Department] D ON D.DepartmentId = S.[DepartmentId]
WHERE D.CollegeId = 5
GROUP BY S.DepartmentId
HAVING SUM(CASE WHEN [IsFTEStudent] = 1 THEN 1 ELSE 0 END) > 0

Depending on the actual datatype and values of IsFTEStudent, we might be able to simplify the aggregate expressions a little. If it's an integer with 0/1 values for example, then:

SELECT S.[DepartmentId],
    AVG([IsFTEStudent] * 1.0) AS [AverageOfFTEStudents]
FROM dbo.[Student] S
INNER JOIN [dbo].User U ON ON S.[StudentId] = U.[UserId]
INNER JOIN dbo.[Department] D ON D.DepartmentId = S.[DepartmentId]
WHERE D.CollegeId = 5
GROUP BY S.DepartmentId
HAVING SUM([IsFTEStudent]) > 0

SQL query to exclude records if it matches an entry in another table (such as holiday dates)

THe following query should get you a list of applications that DO NOT have a holiday defined for the CURRENT date.

SELECT apps.ApplicationName, apps.isavailable 
FROM dbo.Applications apps
WHERE apps.ApplicationName = @AppName
    AND NOT EXISTS 
( SELECT * 
  FROM Holidays 
  WHERE ApplicationId = apps.ApplicationId
     AND CONVERT(VARCHAR,getdate(),101) = CONVERT(VARCHAR,holidaydate,101)
)

Basically what we do is select everything where it does not have a match.

Exclude records that meet certain criteria by row

Please group your data first by using dense_rank and row_number, then benefiting from temp tables, get the data you are looking for.

CREATE TABLE #temp (Patient VARCHAR(50), Action VARCHAR(3))
Insert INTO #temp VALUES
('james','REG'),
('james','CUR'),
('Jacob','REG'),
('Jacob','REG'),
('Main','CUR'),
('Main','REG'),
('Lucy','REG'),
('Lucy','CUR'),
('Lucy','CUR')

SELECT *, DENSE_RANK() OVER (ORDER BY Patient ASC) GroupNo,
          ROW_NUMBER() OVER (partition BY Patient ORDER BY Patient ASC) GroupOrder
INTO #PatientsWithGroup
FROM #temp

SELECT MIN(c1.GroupNo) GroupNo
INTO #PatsToEliminate
FROM #PatientsWithGroup c1
INNER JOIN #PatientsWithGroup c2 ON c1.GroupNo=c2.GroupNo
WHERE (c1.GroupOrder=1 AND c1.Action='REG') AND (c2.GroupOrder = 2 AND c2.Action='CUR')
HAVING COUNT(c1.Patient)<3

SELECT *
FROM #PatientsWithGroup p
WHERE p.GroupNo NOT IN (SELECT GroupNo FROM #PatsToEliminate)

How to exclude records based on values in 2 other tables

You need to change to OR:

SELECT Table1.ID 
FROM Table1 
WHERE Table1.Column1 = 'A'
AND (
Table1.ID NOT IN (SELECT Table2.ID FROM Table2 WHERE Table2.Column2 = 'X')
OR 
Table1.ID NOT IN (SELECT Table3.ID FROM Table3 WHERE Table3.Column3 = 'Y'))

or you could rewrite it to:

SELECT Table1.ID 
FROM Table1 
WHERE Table1.Column1 = 'A'
EXCEPT (
  SELECT Table2.ID FROM Table2 WHERE Table2.Column2 = 'X' 
  UNION ALL
  SELECT Table3.ID FROM Table3 WHERE Table3.Column3 = 'Y'
)

EDIT:

SELECT DISTINCT Table1.ID 
FROM Table1 
LEFT JOIN Table2
  ON Table1.ID = Table2.ID
 AND Table2.Column2 = 'X'
LEFT JOIN Table3
  ON Table1.ID = Table3.ID
 AND Table3.Column3 = 'Y'
WHERE Table1.Column1 = 'A'
  AND (Table2.ID IS NULL AND Table3.ID IS NULL);

---

Related Topics