SQL QUERY ISSUE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL QUERY ISSUE

Author

Topic

Tyecom
Starting Member

12 Posts

Posted - 2011-04-08 : 12:11:44

I'll try to make this example as brief as possible. I have three tables say, Employees, Employeesbk and Territories. Employees and Employeesbk are basically the same tables, with Employeesbk serving as a backup. I wrote a query to return all rows are different between these two tables. This query is below:

USE NORTHWIND

Select LastName, FirstName, City, Region, Extension, TerritoryID

FROM dbo.Employees, dbo.EmployeeTerritories

WHERE NOT EXISTS

(SELECT 1 FROM dbo.Employeesbk

WHERE dbo.Employees.EmployeeID = dbo.Employeesbk.EmployeeID

AND dbo.Employees.EmployeeID = dbo.EmployeeTerritories.EmployeeID

AND dbo.Employees.LastName = dbo.Employeesbk.LastName

AND dbo.Employees.FirstName = dbo.Employeesbk.FirstName

AND dbo.Employees.City = dbo.Employeesbk.City

AND dbo.Employees.Region = dbo.Employeesbk.Region

AND dbo.Employees.Extension = dbo.Employeesbk.Extension

)

The results are as expected. Now... The problem I’m having is trying to take the query one step further. I also want to narrow the results down to TerritoryID (AND dbo.EmployeeTerritories.TerritoryID = '33607'). This is how my sql statement at present:

USE NORTHWIND

Select LastName, FirstName, City, Region, Extension, TerritoryID

FROM dbo.Employees, dbo.EmployeeTerritories

WHERE NOT EXISTS

(SELECT 1 FROM dbo.Employeesbk

WHERE dbo.Employees.EmployeeID = dbo.Employeesbk.EmployeeID

AND dbo.Employees.EmployeeID = dbo.EmployeeTerritories.EmployeeID

AND dbo.EmployeeTerritories.TerritoryID = '33607'

AND dbo.Employees.LastName = dbo.Employeesbk.LastName

AND dbo.Employees.FirstName = dbo.Employeesbk.FirstName

AND dbo.Employees.City = dbo.Employeesbk.City

AND dbo.Employees.Region = dbo.Employeesbk.Region

AND dbo.Employees.Extension = dbo.Employeesbk.Extension

)

The change is highlighted. This query still returns all row that are different instead of narrowing them down to “TerritoryID”. Am I approaching this correctly? Should I take the results from the first query, save to tmp table, then run a select statement for narrow it down to TerritoryID? Is there any way to accomplish this in a single query? Thanks in advance

Employees/ Employeebk Tables

EmployeeID
LastName
FirstName
Title
TitleOfCourtesy
BirthDate
HireDate
Address
City
Region
PostalCode
Country
HomePhone
Extension
Photo
Notes
ReportsTo
PhotoPath

EmployeesTerritories Table

EmployeeID
TerritoryID

y2n
Starting Member

5 Posts

Posted - 2011-04-08 : 12:56:28

You have your check for the TerritoryID at the wrong level. It is filtering the subquery, then when the WHERE NOT EXISTS is applied it returns those records since they were filtered out at the subquery. Pay attention to the WHERE clause below.

USE NORTHWIND

Select LastName, FirstName, City, Region, Extension, TerritoryID

FROM dbo.Employees, dbo.EmployeeTerritories

WHERE NOT EXISTS
(SELECT 1 FROM dbo.Employeesbk
WHERE dbo.Employees.EmployeeID = dbo.Employeesbk.EmployeeID
AND dbo.Employees.EmployeeID = dbo.EmployeeTerritories.EmployeeID
--AND dbo.EmployeeTerritories.TerritoryID = '33607'
AND dbo.Employees.LastName = dbo.Employeesbk.LastName
AND dbo.Employees.FirstName = dbo.Employeesbk.FirstName
AND dbo.Employees.City = dbo.Employeesbk.City
AND dbo.Employees.Region = dbo.Employeesbk.Region
AND dbo.Employees.Extension = dbo.Employeesbk.Extension
)
AND AND dbo.EmployeeTerritories.TerritoryID = '33607'

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-08 : 13:33:26

Why you do not use EXCEPT instead of NOT EXISTS, so the query will be very more readable.
Try it:


;WITH C AS
(SELECT EmployeeID, LastName, FirstName, City, Region, Extension
   FROM dbo.Employees
 EXCEPT
 SELECT EmployeeID, LastName, FirstName, City, Region, Extension
   FROM dbo.Employeesbk
)
SELECT LastName, FirstName, City, Region, Extension, TerritoryID
  FROM C 
  JOIN dbo.EmployeeTerritories E
    ON C.EmployeeID = E.EmployeeID
 WHERE E.TerritoryID = '33607';

______________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources