June 28, 2006 at 2:04 pm
MS SQL Server 2000, SQL Query Analyzer 8.00.194, Enterprise Manager 8.0, Win XP Pro SP2
I need to write SQL to find missing values in a linking table.
Say I have a table like this, all INTEGER NOT NULL; ID is primary key
ID....OrgID....RoleID
1.......9..........3
2.......9..........47
3.......9..........2
4.......10..........47
5.......10..........3
6.......11..........2
7.......12..........18
9.......12..........3
10.......13..........47
13.......13..........2
I need to find all DISTINCT OrgID values where RoleID is <> 2.
In my tiny example, OrgID 10 and 12 don’t have a RoleID of 2. So I would like to see a result set like this:
OrgID
10
12
The end game is to add rows to the linking table for these orgs, with RoleID = 2.
I have tried various approaches but am stuck. Any help appreciated.
June 28, 2006 at 2:08 pm
SELECT t1.OrgID
FROM YourTable As t1
WHERE NOT EXISTS (
SELECT *
FROM YourTable As t2
WHERE t1.OrgID = t2.OrgID
AND t2.RoleID = 2
)
June 28, 2006 at 2:20 pm
With a slight change:
SELECT DISTINCT t1.OrgID
That is IT!!!!!!!
Thanks, PW
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply