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
How to Check If More Than One Column Is Not Null Per Record
Delete Rows With Date Older Than 30 Days With SQL Server Query
How to Retrieve Records for Last 30 Minutes in Ms SQL
When to Use Single Quotes, Double Quotes, and Backticks in MySQL
Select First Row in Each GROUP BY Group
SQL Server Dynamic PIVOT Query
Difference Between "INNER JOIN" and "OUTER JOIN"
Sql - How to Sum/Aggregate Certain Rows in a Table
Selecting the First Day of the Month in Hive
How to Get Previous Row Data in SQL Server
How to Replace Single-Quote With Double-Quote in SQL Query - Oracle 10G
What Is the Best Datatype for Storing Urls in a MySQL Database
Sql Select Radius Search Based on Latitude Longitude
Comparing Two Count Results Using SQL