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.
| 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_COUNTFROM 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_IDWHERE (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. |
 |
|
|
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." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-28 : 16:58:43
|
| Might perform better to use EXISTS rather than COUNT(*) |
|
|
|
|
|
|
Select User by Name AND/OR ID