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 2012 Forums
 Transact-SQL (2012)
 Select User by Name AND/OR ID

Author  Topic 

eljapo4
Posting Yak Master

100 Posts

Posted - 2015-04-28 : 09:44:32
Hi Guys,
I'm working with the following query to try and determine if a user already exists in a DB before adding a new user.

SELECT COUNT (*) AS ENTITY_COUNT
FROM ORG_ENTITY
INNER JOIN ORG_ENTITY_TYPE ON ORG_ENTITY.N_TYPE_ID = ORG_ENTITY_TYPE.N_TYPE_ID
LEFT OUTER JOIN E_MAIL ON ORG_ENTITY.N_ORG_ENTY_ID = E_MAIL.N_ORG_ENTY_ID
WHERE (ORG_ENTITY.M_ORG_FIRST = 'paul' AND ORG_ENTITY.M_ORG_LAST = 'test')
OR ORG_ENTITY.N_USER_ID = 'TST1'

the thing is there could be an existing DB record with the same name but the new user to be added may have a different N_USER_ID in which case I want to reject the new entry. But if the name is the same and the N_USER_ID already exist then I want to reject the new entry. I'm returning an Int from my query. Can someone enlighten on how to do this in SQL please?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-28 : 10:32:52
How is N_USER_ID assigned?

The thing is, with the query you posted, You will return 0 if there is no row for "paul test" and also no row where the userid is "TST1". However, since logically you would never have duplicate user ids (otherwise you have bigger problems than this), it would be enough to test the userid. Put another way, if there IS a userid "TST1", you don't care what the name is.
Go to Top of Page

eljapo4
Posting Yak Master

100 Posts

Posted - 2015-04-28 : 10:37:22
Returning 0 is fine as that means the new entry the user is trying to add can be added as no duplicate exists. The USERID will always be unique (it's based on a company NTID) but there could be 2 persons with the same name but they'd have a different USERID. So I suppose what you're saying here could be right - "if there IS a userid "TST1", you don't care what the name is."
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-28 : 16:58:43
Might perform better to use EXISTS rather than COUNT(*)
Go to Top of Page
   

- Advertisement -