Use function in JOIN criteria or in WHERE clause

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)

Use function in JOIN criteria or in WHERE clause

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-05-26 : 13:23:10

I tried this simple select to get all the EMPLIDs that have MSDN subscriptions. There is a function that translates the EMPLID to another Employee Number. The function works by itself:

This function simply returns a cross-referenced EMPLID:

--Usage dbo.XRefEMPLID:
--select * from HR_NSV_PM WHERE EMPLID = dbo.XRefEMPLID('A561491')
--First, I tried a JOIN instead of a WHERE clause which gave me an error that Subquery returned more than 1 value:

select HR.* from HR_NSV_PM HR
inner join MSDN_Subscriptions MSDN ON HR.EMPLID = dbo.XRefEMPLID(MSDN.EmployeeID)

Then, I removed the JOIN and I tried some where clauses and got no records:

--where exists (select HR.EMPLID from MSDN_Subscriptions where MSDN_Subscriptions.EmployeeID = dbo.XRefEMPLID(HR.EMPLID))
--where exists (select MSDN_Subscriptions.EmployeeID from MSDN_Subscriptions where MSDN_Subscriptions.EmployeeID = dbo.XRefEMPLID(HR.EMPLID))

Thank you in advance for any help here.

Duane

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-26 : 13:30:24

In the join you are giving MSDN.EmployeeID to the function.
In the exists() you are giving the HR.EMPLID to the function.

No, you're never too old to Yak'n'Roll if you're too young to die.

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-05-26 : 13:41:58

quote:
Originally posted by webfred

In the join you are giving MSDN.EmployeeID to the function.
In the exists() you are giving the HR.EMPLID to the function.

Thank you for your response, but I don't understand what you mean. In the JOIN above, I got that error. And when I tried the 2 WHERE clauses (separately, of course), I got no records. If you could clarify, maybe I could get this.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources