Urgent: Left Join Query unexpected results!

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

Other Forums

MS Access

Urgent: Left Join Query unexpected results!

Author

Topic

robertnzana
Starting Member

42 Posts

Posted - 2008-05-20 : 09:45:49

I have 2 tables - [Emergency Roster USGS 2008-10] and [dbo_tblUSGSManaagers]

I'm trying to delete everyone in table 1 that is NOT in table 2.

I "left join" linked them by 3 fields, basically Name, City and State.

When I run the actual query I manually verified a few records and they exist in both tables! I'm confused. Here's the query...

SELECT [Emergency Roster USGS 2008-10].Org, [Emergency Roster USGS 2008-10].[Org Desc], [Emergency Roster USGS 2008-10].Name, [Emergency Roster USGS 2008-10].[Duty Station City Desc], [Emergency Roster USGS 2008-10].[Duty Station State Desc], dbo_USGSEmployees.EmployeeName, dbo_USGSEmployees.City, dbo_USGSEmployees.State
FROM [Emergency Roster USGS 2008-10] LEFT JOIN dbo_USGSEmployees ON ([Emergency Roster USGS 2008-10].[Duty Station State Desc] = dbo_USGSEmployees.State) AND ([Emergency Roster USGS 2008-10].[Duty Station City Desc] = dbo_USGSEmployees.City) AND ([Emergency Roster USGS 2008-10].[Name] = dbo_USGSEmployees.[EmployeeName])
WHERE (((dbo_USGSEmployees.EmployeeName) Is Null) AND ((dbo_USGSEmployees.City) Is Null) AND ((dbo_USGSEmployees.State) Is Null));

For example, "John Smith" from "Jupiter" and "FL", show up in both tables, but he also shows up as "unmatched". Please help me. This is urgent.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-20 : 09:58:32

This is your query

SELECT		[Emergency Roster USGS 2008-10].Org,
		[Emergency Roster USGS 2008-10].[Org Desc],
		[Emergency Roster USGS 2008-10].Name,
		[Emergency Roster USGS 2008-10].[Duty Station City Desc],
		[Emergency Roster USGS 2008-10].[Duty Station State Desc],
		dbo_USGSEmployees.EmployeeName, dbo_USGSEmployees.City,
		dbo_USGSEmployees.State
FROM		[Emergency Roster USGS 2008-10]
LEFT JOIN	dbo_USGSEmployees ON dbo_USGSEmployees.State = [Emergency Roster USGS 2008-10].[Duty Station State Desc]
			AND dbo_USGSEmployees.City = [Emergency Roster USGS 2008-10].[Duty Station City Desc]
			AND dbo_USGSEmployees.[EmployeeName] = [Emergency Roster USGS 2008-10].[Name]
WHERE		dbo_USGSEmployees.EmployeeName IS NULL
		AND dbo_USGSEmployees.City IS NULL
		AND dbo_USGSEmployees.State IS NULL

If you tell the query to only pick the records from dbo_USGSEmployees where State, City and EmployeeName IS NULL and then JOIN the records where these columns are NULL, what do you expect?

E 12°55'05.25"
N 56°04'39.16"

robertnzana
Starting Member

42 Posts

Posted - 2008-05-20 : 10:31:45

I was trying to say "SELECT all records from [Emergency Roster USGS 2008-10] that are NOT in the dbo_USGSEmployees table."

Isn't this correct???

robertnzana
Starting Member

42 Posts

Posted - 2008-05-20 : 10:40:50

Sorry, but let me back up...

I'm actually trying to do this...

SELECT dbo_USGSEmployees.*
FROM [Emergency Roster USGS 2008-10] RIGHT JOIN dbo_USGSEmployees ON ([Emergency Roster USGS 2008-10].[Duty Station City Desc] = dbo_USGSEmployees.City) AND ([Emergency Roster USGS 2008-10].[Duty Station State Desc] = dbo_USGSEmployees.State) AND ([Emergency Roster USGS 2008-10].Name = dbo_USGSEmployees.EmployeeName)
WHERE ((([Emergency Roster USGS 2008-10].Name) Is Null));

I want everyone in dbo_USGSEmployees that DOES NOT APPEAR in [Emergency Roster USGS 2008-10]. When I run the query though I hand verify the records that appear and they ARE in BOTH tables.

My only thought is this: The [Emergency Roster USGS 2008-10] table was IMPORTED from an EXCEL spreadsheet. I noticed that all "wierd records" had a "city" that was blank/null/etc... Perhaps the data is being handled differently?

Does that make sense?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-20 : 14:38:23

Yes, it is correct. I was just baffled by the multiple IS NULL checks.

SELECT		[Emergency Roster USGS 2008-10].Org,
		[Emergency Roster USGS 2008-10].[Org Desc],
		[Emergency Roster USGS 2008-10].Name,
		[Emergency Roster USGS 2008-10].[Duty Station City Desc],
		[Emergency Roster USGS 2008-10].[Duty Station State Desc],
		dbo_USGSEmployees.EmployeeName, dbo_USGSEmployees.City,
		dbo_USGSEmployees.State
FROM		[Emergency Roster USGS 2008-10]
LEFT JOIN	dbo_USGSEmployees ON dbo_USGSEmployees.State = [Emergency Roster USGS 2008-10].[Duty Station State Desc]
			AND dbo_USGSEmployees.City = [Emergency Roster USGS 2008-10].[Duty Station City Desc]
			AND dbo_USGSEmployees.[EmployeeName] = [Emergency Roster USGS 2008-10].[Name]
WHERE		dbo_USGSEmployees.EmployeeName IS NULL

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources