CODEWITHSUNDEEP
sql-servert-sql
Mitch
Mitch

Reputation: 3

TSQL query - return value from other row

I currently have a query that returns this

USER    GROUP   GROUPCODE
U1      G1      abcd
U1      G2 P    vxyz
U2      G1      abcd
U2      G2      lmno

The query looks like this

select u.user, g.group, g.groupcode
FROM u
INNER JOIN g ON u.user = g.user

Where users are in 'P' groups, I want to get the group and groupcode for the matching 'non-P' groups. So the above would be like this.

USER    GROUP   GROUPCODE
U1      G1      abcd
U1      G2      lmno
U2      G1      abcd
U2      G2      lmno

I'm using a very limited proprietary system that doesn't support variables or anything that requires full-text indexed columns. Is there a way to do this or am I dreaming?

EDIT: I'm trying to replace GROUP and GROUPCODE for rows conataining 'P' groups. The query is sort of like an enrollment file. 'P' groups are like a special version of normal groups. But for enrollment, users in P groups should just be put into the normal version.

Getting the GROUP can be done with this

SELECT CASE WHEN (RIGHT(SU.FULLNAME, 2) = ' P') 
THEN (substring(g.group, 1, (len(g.group) - 2))) 
ELSE g.group
END AS GROUP

But then I cant't figure out how to get the matching GROUPCODE

Upvotes: 0

Views: 51

Answers (2)

Ranjan Christopher
Ranjan Christopher

Reputation: 1

select u.user, g.group, g.groupcode
FROM u
INNER JOIN g ON u.user = g.user
where r.group no like '%P%'

Upvotes: 0

neer
neer

Reputation: 4082

Suppose that GROUP is always like 'AllText' P.

SELECT
  u.user, 
  CASE WHEN g.group LIKE '%P' THEN LEFT(g.group, 2) 
       ELSE g.group AS group, 
  CASE WHEN g.group LIKE '%P' THEN  (
                                         SELECT TOP 1 gro.groupcode 
                                         FROM g AS gro 
                                         WHERE gro.group = LEFT(g.group , 2)
                                     ) 
       ELSE g.groupcode END AS groupcode, 
FROM 
    u INNER JOIN 
    g ON u.user = g.user

Upvotes: 1

Related Questions