Analyzing data from same tables in diferent db instances.

Question

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

Answer 1

It seems like for any given activityin, the same rolein value applies. Thus if database A (db1) has at least one association for each for the rolein/activityin relationships, you can populate database B (db2) with a simple one-time update query:

UPDATE db2.taskperformance
SET db2.taskperformance.rolein =
(SELECT db1.taskperformance.rolein
FROM db1.taskperformance
WHERE db1.taskperformance.activityin = db2.taskperformance.activityin);

I strongly advise backing up the database first before running the query as it will change all the rolein values, and if my assumption is incorrect, you may have bad data.

EDIT

Although I think it could be done in one query, it's beyond my knowledge of sql. However I think the following will work: Create a temporary table that contains all the rolein values for each activityin value in database A. This table essentially becomes an activities table, except now you have numbers replacing the activityid values, which can be looked up to fill in the missing values of rolein for database B.

CREATE TEMPORARY TABLE db2.ttable
SELECT db1.taskperformance.rolein, db1.activities.activityin
FROM db1.taskperformance, db1.activities
WHERE db1.taskperformance.activityin = db1.activities.activityin;

From the original data, this yields:

rolein     activityin 
1          1          
1          2          
2          3          
2          4          
3          6          
3          7          
3          7

Now you should be able to run the update query against this temporary table:

UPDATE db2.taskperformance
SET db2.taskperformance.rolein =
(SELECT db2.ttable.rolein
FROM db2.ttable
WHERE db2.taskperformance.activityin = db2.ttable.activityin);

The problem with this will be if you have unique values in database B for activityin that do not occur in database A.

Finally:

DROP TEMPORARY TABLE db2.ttable;

For completeness' sake.

Answer 2

Finally I've got it.

It seems that I just didn't develop the section of the brain that helps to make queries.

Here's the result

select distinct(  a.rolein ) , a.activityin, b.activityin 
from 
    taskperformance@dm_prod a,
    taskperformance b, 
    activities@dm_prod c,
    activities d
where b.rolein = 0
    and b.activityin = d.activityin 
    and d.activityid = c.activityid
    and c.activityin = a.activityin
order by b.activityin , a.activityin

Thank you JYelton, your questions helped me to understand my own question better.