sql query to compare data

[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 query 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 query 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

 

[PHV]

SELECT A.SiteId, A.userid, 'both' As ComingFrom
FROM A INNER JOIN B ON A.SiteId=B.SiteId AND A.userid=B.userid
UNION SELECT A.SiteId, A.userid, 'A'
FROM A LEFT JOIN B ON A.SiteId=B.SiteId AND A.userid=B.userid
WHERE B.SiteId Is Null
UNION SELECT B.SiteId, B.userid, 'B'
FROM A RIGHT JOIN B ON A.SiteId=B.SiteId AND A.userid=B.userid
WHERE A.SiteId Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[aajaydee]

it does not work
it give me error time out