Find Records which are missing.

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

Transact-SQL (2000)

Find Records which are missing.

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_ID
Equipment_ID

Every 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_ID
A001--------------------Cleveland 1
A002--------------------Cleveland 1
Cleveland 1-------------Cleveland 1

Example 2:
In this example the Parent Record is missing. There is no record where the Equipment_ID is the same as the Parent_ID
Equipment_ID---------Parent_ID
B001-----------------Cleveland 2
B002-----------------Cleveland 2

Thank 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 like
Select * from #t t where (t.Parent_ID != t.Equipment_ID) and t.parent_ID
--or
Select * 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources