I am sure making a silly mistake but I can't figure what:
In SQL Server 2005 I am trying select all customers except those who have made a reservation before 2 AM.
When I run this query:
SELECT idCustomer FROM reservations
WHERE idCustomer NOT IN
(SELECT distinct idCustomer FROM reservations
WHERE DATEPART ( hour, insertDate) < 2)
I get 0 results.
But
SELECT idCustomer FROM reservations
returns 152.000 results and the "NOT IN" part:
SELECT distinct idCustomer FROM reservations
WHERE DATEPART ( hour, insertDate) < 2
returns only 284 rows
This question is related to
sql
sql-server
Sorry if I've missed the point, but wouldn't the following do what you want on it's own?
SELECT distinct idCustomer FROM reservations
WHERE DATEPART(hour, insertDate) >= 2
It's always dangerous to have NULL in the IN list - it often behaves as expected for the IN but not for the NOT IN:
IF 1 NOT IN (1, 2, 3, NULL) PRINT '1 NOT IN (1, 2, 3, NULL)'
IF 1 NOT IN (2, 3, NULL) PRINT '1 NOT IN (2, 3, NULL)'
IF 1 NOT IN (2, 3) PRINT '1 NOT IN (2, 3)' -- Prints
IF 1 IN (1, 2, 3, NULL) PRINT '1 IN (1, 2, 3, NULL)' -- Prints
IF 1 IN (2, 3, NULL) PRINT '1 IN (2, 3, NULL)'
IF 1 IN (2, 3) PRINT '1 IN (2, 3)'
Given it's SQL 2005, you can also try this It's similar to Oracle's MINUS command (opposite of UNION)
But I would also suggest adding the DATEPART ( hour, insertDate) column for debug
SELECT idCustomer FROM reservations
EXCEPT
SELECT idCustomer FROM reservations WHERE DATEPART ( hour, insertDate) < 2
SELECT Reservations.idCustomer FROM Reservations (nolock)
LEFT OUTER JOIN @reservations ExcludedReservations (nolock) ON Reservations.idCustomer=ExcludedReservations.idCustomer AND DATEPART(hour, ExcludedReservations.insertDate) < 2
WHERE ExcludedReservations.idCustomer IS NULL AND Reservations.idCustomer IS NOT NULL
GROUP BY Reservations.idCustomer
[Update: Added additional criteria to handle idCustomer being NULL, which was apparently the main issue the original poster had]
SELECT MIN(A.maxsal) secondhigh
FROM (
SELECT TOP 2 MAX(EmployeeBasic) maxsal
FROM M_Salary
GROUP BY EmployeeBasic
ORDER BY EmployeeBasic DESC
) A
select * from table_name where id=5 and column_name not in ('sandy,'pandy');
Sorry if I've missed the point, but wouldn't the following do what you want on it's own?
SELECT distinct idCustomer FROM reservations
WHERE DATEPART(hour, insertDate) >= 2
Given it's SQL 2005, you can also try this It's similar to Oracle's MINUS command (opposite of UNION)
But I would also suggest adding the DATEPART ( hour, insertDate) column for debug
SELECT idCustomer FROM reservations
EXCEPT
SELECT idCustomer FROM reservations WHERE DATEPART ( hour, insertDate) < 2
Sorry if I've missed the point, but wouldn't the following do what you want on it's own?
SELECT distinct idCustomer FROM reservations
WHERE DATEPART(hour, insertDate) >= 2
Given it's SQL 2005, you can also try this It's similar to Oracle's MINUS command (opposite of UNION)
But I would also suggest adding the DATEPART ( hour, insertDate) column for debug
SELECT idCustomer FROM reservations
EXCEPT
SELECT idCustomer FROM reservations WHERE DATEPART ( hour, insertDate) < 2
select * from table_name where id=5 and column_name not in ('sandy,'pandy');
It's always dangerous to have NULL in the IN list - it often behaves as expected for the IN but not for the NOT IN:
IF 1 NOT IN (1, 2, 3, NULL) PRINT '1 NOT IN (1, 2, 3, NULL)'
IF 1 NOT IN (2, 3, NULL) PRINT '1 NOT IN (2, 3, NULL)'
IF 1 NOT IN (2, 3) PRINT '1 NOT IN (2, 3)' -- Prints
IF 1 IN (1, 2, 3, NULL) PRINT '1 IN (1, 2, 3, NULL)' -- Prints
IF 1 IN (2, 3, NULL) PRINT '1 IN (2, 3, NULL)'
IF 1 IN (2, 3) PRINT '1 IN (2, 3)'
SELECT Reservations.idCustomer FROM Reservations (nolock)
LEFT OUTER JOIN @reservations ExcludedReservations (nolock) ON Reservations.idCustomer=ExcludedReservations.idCustomer AND DATEPART(hour, ExcludedReservations.insertDate) < 2
WHERE ExcludedReservations.idCustomer IS NULL AND Reservations.idCustomer IS NOT NULL
GROUP BY Reservations.idCustomer
[Update: Added additional criteria to handle idCustomer being NULL, which was apparently the main issue the original poster had]
It's always dangerous to have NULL in the IN list - it often behaves as expected for the IN but not for the NOT IN:
IF 1 NOT IN (1, 2, 3, NULL) PRINT '1 NOT IN (1, 2, 3, NULL)'
IF 1 NOT IN (2, 3, NULL) PRINT '1 NOT IN (2, 3, NULL)'
IF 1 NOT IN (2, 3) PRINT '1 NOT IN (2, 3)' -- Prints
IF 1 IN (1, 2, 3, NULL) PRINT '1 IN (1, 2, 3, NULL)' -- Prints
IF 1 IN (2, 3, NULL) PRINT '1 IN (2, 3, NULL)'
IF 1 IN (2, 3) PRINT '1 IN (2, 3)'
SELECT Reservations.idCustomer FROM Reservations (nolock)
LEFT OUTER JOIN @reservations ExcludedReservations (nolock) ON Reservations.idCustomer=ExcludedReservations.idCustomer AND DATEPART(hour, ExcludedReservations.insertDate) < 2
WHERE ExcludedReservations.idCustomer IS NULL AND Reservations.idCustomer IS NOT NULL
GROUP BY Reservations.idCustomer
[Update: Added additional criteria to handle idCustomer being NULL, which was apparently the main issue the original poster had]
Given it's SQL 2005, you can also try this It's similar to Oracle's MINUS command (opposite of UNION)
But I would also suggest adding the DATEPART ( hour, insertDate) column for debug
SELECT idCustomer FROM reservations
EXCEPT
SELECT idCustomer FROM reservations WHERE DATEPART ( hour, insertDate) < 2
SELECT MIN(A.maxsal) secondhigh
FROM (
SELECT TOP 2 MAX(EmployeeBasic) maxsal
FROM M_Salary
GROUP BY EmployeeBasic
ORDER BY EmployeeBasic DESC
) A
SELECT Reservations.idCustomer FROM Reservations (nolock)
LEFT OUTER JOIN @reservations ExcludedReservations (nolock) ON Reservations.idCustomer=ExcludedReservations.idCustomer AND DATEPART(hour, ExcludedReservations.insertDate) < 2
WHERE ExcludedReservations.idCustomer IS NULL AND Reservations.idCustomer IS NOT NULL
GROUP BY Reservations.idCustomer
[Update: Added additional criteria to handle idCustomer being NULL, which was apparently the main issue the original poster had]
Source: Stackoverflow.com