Self Join -querying within a table

Question

Self Join -querying within a table

MuditGupta

SSCrazy

Points: 2596
More actions
September 17, 2008 at 7:18 am

#197869

Hi there
I am trying to do a query on a table. It is sort of a self join.
For eg. The table contains 5 columns namely:
ID, Code1,Code2,Link
Id could be SSN or some unique # which is unique for every person.
Code1 and code2 are dept_code and manager_code
Link is internally generated filed for linking this table with other tables.When ever a person moves to different branch or gets different manager, his Link changes.
This table doesn't discard previous record of same person. there are columns for year & month as well.
So, for eg:
ID Code1 code2 Link
1ABZ
2A'B'Y
1ACX
1A''DW
1ABZ (This is not a duplicated row. Year & month can be diffrent)
Now, I wanna get only those records where A person has different Link when Code1 or code2 values changed.
How can I do that?
select
a.Id,a.code1,a.code2,a.link,b.code1,b.code2,b.link
from
table1 as a
inner join
table1 as b
ON
a.ID=b.Id
where
a.code1<>b.code1
OR
a.code2<>b.code2
I am not sure if this is correct? Please Advise.
thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 735942 More actions · Answer 1

I'm not sure what you mean by code1 or 2 changed. It's a little confusing to follow your example.

Have you tried distinct to limit the records and then looking for what things are different between the rows you want and those you don't?

MuditGupta SSCrazy Points: 2596 More actions · Answer 2

Sorry for the confusion.

A general example to explain:

Alex (ID#1) was working under John(code2- say xxx) --dept being Finance(code1- yy) (this was in Yr 2007 & QuarterID - 1)

This record is same for QuarterID -2,3,4 for same Yr. So, for 07- ALex has 4 different Link identifying quarters.

Now, In 08,

quarter 1:

Alex gets new manager(code2- abc) So, he gets new Link -- same for Quarter2

Come quarter3:

Alex moves to new dept so, he get another Link.

In a way, Link changes every quarter-- sometimes because of change in quarter OR change in value of Code1 OR change in value of Code1

And I want to write a query which gives back only those records where Link changed because of new values for code1 or code2

Hope this presents clearer picture

MuditGupta SSCrazy Points: 2596 More actions · Answer 3

probably following condition for self join

a.ID=b.ID AND

a.Link<> b.link AND

a.code1<> b.code1 OR

a.code2<> b.code2

Does this makes sense?

Btw, it just occured to me that if code1(dept_code) changes -- Link will get new value then no need to consider manager_code(code2)

And there can be cases when only code2 has different value-- then also Link will attain new value

Link changes on following condition:

Year changes, Quarter changes, code1 changes , code2 changes

I am interested in getting records for given year where Link got new value because of different code1 OR code2. I do not want to consider new values of Link for changes in QuarterID

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

Maybe I missed the point you were trying to make, but it sounds like you may need to come up with an ordered list first, likely in a CTE, before you do the self join. I'd use the ROW_NUMBER() function in your CTE query:

SELECT ...

ROW_NUMBER() OVER (PARTITION BY id ORDER BY yr, QuarterID DESC) AS Recentness

FROM ...

then when you do your self join:

...

ON a.id = b.id AND a.Recentness = b.Recentness + 1

AND (a.Code1 <> b.Code1 OR a.Code2 <> b.Code2)

MuditGupta SSCrazy Points: 2596 More actions · Answer 5

Thanks for your suggestion. It helped me in sorting out my issues...