Help with Query - SQL Server Forums

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

Transact-SQL (2005)

Help with Query

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-05-12 : 07:59:37

I am trying to isolate any orpahned CONTACT records. An orphan would be created if a USER record was deleted.

Within the USER table, there is a user_id. I thought I could compare the CONTACTS rn_create_user and rn_edit_user to the USERS user_id. In cases where there are no matches, this would indicate an orphaned CONTACT record.

I am getting a little lost coming up with the logic needed to create this query (joins, etc...). Any help would be appreciated with this query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 08:01:55

Can you provide your tables structures along with some sample data?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-12 : 08:03:36

SELECT c.*
FROM Contacts AS c
LEFT JOIN User AS u ON u.User_ID IN (c.m_create_user, c.m_edit_user)
WHERE u.user_id IS NULL

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-12 : 08:06:08

[code]select c.*
from
Contacts c
where not exists(select * from User u where c.user_id = u.rn_create_user or c.user_id = u.rn_edit_user)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-05-12 : 08:11:38

Not sure if this makes sense....
Condensed table structure:

Table Name: Users
Column: Users_Id
Data Type: binary
Is Nullable: NO
Max Chars: 8

Table Name: Users
Column: Rn_Create_User
Data Type: binary
Is Nullable: YES
Max Chars: 8

Table Name: Users
Column: Rn_Edit_User
Data Type: binary
Is Nullable: YES
Max Chars: 8

Table Name: Contact
Column: Rn_Create_User
Data Type: binary
Is Nullable: YES
Max Chars: 8

Table Name: Contact
Column: Rn_Edit_User
Data Type: binary
Is Nullable: YES
Max Chars: 8

Sample Data:

Users
User_Id = 0x0000000000000084
Rn_Edit_User = NULL
Rn_Create_User = NULL

User_Id = 0x0000000000000085
Rn_Edit_User = 0x0000000000000084
Rn_Create_User = 0x0000000000000085

Contact
Rn_Edit_User = 0x0000000000000084
Rn_Create_User = 0x0000000000000085

Rn_Edit_User = 0x00000000000000AE
Rn_Create_User = 0x00000000000000BD

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-05-12 : 08:15:03

Both you guys rule, thanks for the quick help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources