OUTER JOIN returning equal records

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 2008 Forums

Transact-SQL (2008)

OUTER JOIN returning equal records

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-05-05 : 10:59:19

There are about 400,500 (all distinct) records in one table and about 370,000 (about 315 distinct) in the other. I know there are about 70,000 that truly do not match. But there are more (most) if I do not pad the zeroes in employeenumber0. Before I did this, there were about 700,000 rows in the outer join, presumably because some rows were counted both times, from the right column and the left. After padding the zeroes, I am getting about 403,000, presumably more of them matching, but still counting the left and the right. But I also noticed in the results, some were matching anyway, so my whole logic is in question. Here is some data:

EmployeeNumber0	EmplID	PaddedEmp
1006270		00001006270	00001006270
1006271		00001006271	00001006271
1006272		00001006272	00001006272
1006273		00001006273	00001006273
1006275		00001006275	00001006275
1006276		00001006276	00001006276
1006277		00001006277	00001006277
1006278		00001006278	00001006278
1006279		00001006279	00001006279

And here is the SQL:

SELECT EmployeeNumber0, EmplID, RIGHT ('00000000000'+ CAST (employeeNumber0 AS varchar), 11) PaddedEmp
FROM _sde.v_HR_NSV
FULL OUTER JOIN dbo.v_R_User
ON RIGHT ('00000000000'+ CAST (employeeNumber0 AS varchar), 11) = EMPLID
GROUP BY employeeNumber0, EMPLID
ORDER BY employeeNumber0, EMPLID

The grouping is because there are some duplicate employeenumber0 columns. Thank you for any suggestions here.

Duane

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-05 : 11:53:12

I'm sorry. I read this many times over, but still not clear what the problem is.

What is the issue you are facing?

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-05-05 : 12:09:42

I was trying to do an unmatched query. I think I figured it out, anyway. I apologize I was not more clear. I thought that an outer join produced an unmatched query, but I didn't think about filtering on NULL columns. It seems to work now (90,000) rows actually are unmatched using 'WHERE EMPLID IS NULL OR employeenumber0 IS NULL'. Thanks for looking, anyway.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources