1

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?

  • How do you define if two workgroups are "similar, but not identical"? – ypercubeᵀᴹ May 23 '13 at 7:30
  • And why LOGONID and RXUSER have different lengths? – ypercubeᵀᴹ May 23 '13 at 7:33
  • @ypercube "Similar, but not identical" is matching the first 3 characters, which you did in the answer you provided. You'd have to ask the vendor that set up our system as to why LOGONID and RXUSER have different lengths. Probably because they have no clue what they are doing. The entire product is riddled with nuances that are flawed or outright wrong. – Jeremy1026 May 23 '13 at 12:07
2

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 < 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
      ) ;
  • My hat goes off for you. This got me within about 4 lines of SQL to get exactly what I needed. Main change was u.workgroup < p.workgroup should have been u.workgroup < p.workgroup since my data isn't all 001 User and 002 Sup. Thanks for your help. – Jeremy1026 May 23 '13 at 12:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.