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 |
AskSQLTeam
Ask SQLTeam Question
0 Posts |
Posted - 2006-04-17 : 10:47:06
|
Guy writes "I have a Table with two fields.Parent_IDEquipment_IDEvery Unique Parent_ID needs to have one record where the Parent_ID and the Equipment_ID matches.I want to find the Parent_ID's where these is no record where the Parent_ID and the Equipment_ID matches.Example:There are two children records and one Parent record. The Parent Record is where the Parent_ID and the Equipment_ID are the same.Equipment_ID------------Parent_IDA001--------------------Cleveland 1A002--------------------Cleveland 1Cleveland 1-------------Cleveland 1Example 2:In this example the Parent Record is missing. There is no record where the Equipment_ID is the same as the Parent_IDEquipment_ID---------Parent_IDB001-----------------Cleveland 2B002-----------------Cleveland 2Thank you so much for your time on this.Guy" |
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-04-17 : 12:44:13
|
[code]Create Table #t ( Equipment_ID varchar(20), Parent_ID varchar(30))Insert Into #t values('A001', 'Cleveland 1')Insert Into #t values('A001', 'Cleveland 1')Insert Into #t values('Cleveland 1', 'Cleveland 1')Insert Into #t values('B001', 'Cleveland 2')Insert Into #t values('B001', 'Cleveland 2')-- the query should be something likeSelect * from #t t where (t.Parent_ID != t.Equipment_ID) and t.parent_ID --orSelect * from #t t where (t.Parent_ID != t.Equipment_ID) and t.parent_ID not in (Select Equipment_ID from #t) DROP TABLE #t[/code]Srinika |
|
|
|
|
|