compare data from different database

[aajaydee]

I have two table A and table b
Both tables have the same DB structure
i.e.
siteID,userid
I am trying to write a stored procedure to compare the userid in both table where there is siteid matched
Both table are from different databases



some records do not match their userid
and some are missing user id in either files
Like to write store proc to find out deference in the userid from table A in table B

Here is example;

Table A

SiteId userid
2316 ZS4598
2316 ZJ1548
2316 M1619
2317 Y8979
2318 K9876
2318 LP90

Table b
SiteID User id
2316 ZS4598
2316 M1619
2317 Y8979
2318 k9876
2318 MN569

Here what I would like to have

Result Match data

2316 zs4598
2316 M1619
2317 Y8979
2318 k9876

and not match data

2316 ZJ1548
2317 LP90


any help will be appreciated

 

[adamroof]

SELECT t1.* FROM mainTable t1
INNER JOIN secondDatabaseName.Owner.mainTableOverHere t2
ON t1.userid = t2.userid

 

[adamroof]

SELECT * FROM mainTable WHERE UserID IN (SELECT t2.UserID FROM secondDatabaseName.Owner.mainTableOverHere t2)

SELECT * FROM mainTable WHERE UserID NOT IN (SELECT t2.UserID FROM secondDatabaseName.Owner.mainTableOverHere t2)