Short version:

How can I map two columns from table A and B if they both have a common identifier which in turn may have two values in column C

Lets say:

A
---
 1 , 2 

B
--- 
 ? , 3 


C 
----- 
45, 2
45, 3

Using table C I know that id 2 and 3 belong to the same item ( 45 ) and thus "?" in table B should be 1.

What query could do something like that?

EDIT

Long version ommited. It was really boring/confusing

EDIT

I'm posting some output here.

From this query:

select distinct( rolein) , activityin from taskperformance@dm_prod where activityin in ( 
    select activityin from activities@dm_prod where activityid in ( 
        select activityid from activities@dm_prod where activityin in ( 
            select distinct( activityin  ) from taskperformance where rolein = 0 
        )
    )
)

I have the following parts:

select distinct( activityin  ) from taskperformance where rolein = 0

Output:

http://question1337216.pastebin.com/f5039557

    select activityin from activities@dm_prod where activityid in ( 
        select activityid from activities@dm_prod where activityin in ( 
            select distinct( activityin  ) from taskperformance where rolein = 0 
        )
    )

Output:

http://question1337216.pastebin.com/f6cef9393

And finally:

select distinct( rolein) , activityin from taskperformance@dm_prod where activityin in ( 
    select activityin from activities@dm_prod where activityid in ( 
        select activityid from activities@dm_prod where activityin in ( 
            select distinct( activityin  ) from taskperformance where rolein = 0 
        )
    )
)

Output:

http://question1337216.pastebin.com/f346057bd

Take for instace activityin 335 from first query ( from taskperformance B) .

It is present in actvities from A.

But is not in taskperformace in A ( but a the related activities: 92, 208, 335, 595 )

Are present in the result. The corresponding role in is: 1