Self Join -querying within a table

  • 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

  • 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?

  • 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

  • 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

  • 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)

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

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

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