Analyzing data from same tables in diferent db instances.
- by Oscar Reyes
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
