Generate List of Missing Relationships I have two tables, CLUSER and CLUSERRX. CLUSER contains all the employees at my company and CLUSERRX contains a list of all the people in CLUSER with a specific workgroup and all of the users from CLUSER with a similar, but not identical workgroup. The columns for these tables are as follows:

CREATE TABLE [dbo].[CLUSER]
  (
     [LOGONID]   [VARCHAR](15) NOT NULL,
     [WORKGROUP] [VARCHAR](30) NOT NULL,
     PRIMARY KEY CLUSTERED ( [LOGONID] ASC ) WITH (...other settings..., 
       FILLFACTOR = 99) ON [PRIMARY]
  )
ON [PRIMARY]

CREATE TABLE [dbo].[CLUSERRX]
  (
     [LOGONID] [VARCHAR](15) NOT NULL,
     [RXUSER]  [VARCHAR](10) NOT NULL,
     CONSTRAINT [USERRX_LOGONID] PRIMARY KEY CLUSTERED 
       ( [LOGONID] ASC, [RXUSER] ASC ) WITH (...other settings..., 
     FILLFACTOR = 99) ON [PRIMARY]
  )
ON [PRIMARY] 


I am able to generate a list of every CLUSERRX.LOGONID, CLUSER.WORKGROUP for that LOGONID, CLUSERRX.RXUSER, and CLUSER.WORKGROUP for the RXUSER using the following query:

SELECT u.LOGONID,
       u.WORKGROUP as 'USER WORKGROUP',
       r.RXUSER,
       p.WORKGROUP as 'PROVIDER WORKGROUP'
FROM   CLUSERRX r
       LEFT JOIN CLUSER u
         ON u.LOGONID = r.LOGONID
       LEFT JOIN cluser p
         ON p.LOGONID = r.RXUSER


What I need to do is take the data in CLUSER and find what relationships are missing in CLUSERRX.

Example data:

CLUSER
|-----------|------------|
|  LOGONID  |  WORKGROUP |
|-----------|------------|
| JCURCIO   | 001 USER   |
| TUSER     | 001 SUP    |
| ATEST     | 001 SUP    |
| MPAGE     | 001 User   |
|-----------|------------|

CLUSERRX
|-----------|------------|
|  LOGONID  |  RXUSER    |
|-----------|------------|
| JCURCIO   | TUSER      |
| JCURCIO   | ATEST      |
|-----------|------------|


Since there is no record for MPAGE in CLUSERRX for TUSER or ATEST that is incorrect and I need to display that.

I feel like I need to use something like:

SELECT u.LOGONID,
       u.WORKGROUP AS 'USER WORKGROUP',
       r.RXUSER,
       p.WORKGROUP AS 'PROVIDER WORKGROUP'
FROM   CLUSERRX r
       LEFT JOIN CLUSER u
              ON u.LOGONID = r.LOGONID
       LEFT JOIN cluser p
              ON p.LOGONID = r.RXUSER
WHERE  ( u.LOGONID NOT IN (SELECT u.LOGONID,
                                  u.WORKGROUP AS 'USER WORKGROUP',
                                  r.RXUSER,
                                  p.WORKGROUP AS 'PROVIDER WORKGROUP'
                           FROM   CLUSERRX r
                                  LEFT JOIN CLUSER u
                                         ON u.LOGONID = r.LOGONID
                                  LEFT JOIN cluser p
                                         ON p.LOGONID = r.RXUSER)
         AND p.WORKGROUP NOT IN (SELECT u.LOGONID,
                                        u.WORKGROUP AS 'USER WORKGROUP',
                                        r.RXUSER,
                                        p.WORKGROUP AS 'PROVIDER WORKGROUP'
                                 FROM   CLUSERRX r
                                        LEFT JOIN CLUSER u
                                               ON u.LOGONID = r.LOGONID
                                        LEFT JOIN cluser p
                                               ON p.LOGONID = r.RXUSER) )


But I have no idea how to format it properly to get the information I need. Can anyone provide any insight on what I may be missing, or if I am even in the right direction?
You have to define what you mean with "similar workgroups" and alter the relevant line but this is what I understand from your wording.

It will show all combinations of users from different workgroups (and similar in an arbitrary way, matching the first 3 characters of group name) that do not appear (in either order) in the cluserrx table:

; WITH UserCombinations AS
    ( SELECT r.logonid AS logonid,
             u.workgroup AS User_workgroup,
             p.logonid AS rxuser
             p.workgroup AS Provider_workgroup
      FROM cluser AS u
        JOIN cluser AS p
          ON  u.workgroup &lt; p.workgroup                     -- not identical workgroups
          AND LEFT(u.workgroup, 3) = LEFT(p.workgroup, 3)   -- but similar
    )
SELECT 
    uc.logonid,
    uc.User_workgroup,
    uc.rxuser
    uc.Provider_workgroup
FROM UserCombinations AS uc
WHERE NOT EXISTS
      ( SELECT *
        FROM cluserrx AS rx
        WHERE rx.logonid = uc.logonid  AND  rx.rxuser = uc.rxuser
           OR rx.logonid = uc.rxuser  AND  rx.rxuser = uc.logonid
      ) ;