Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to compare two different columns in two different databases

Posted on 2014-10-31
6
Medium Priority
?
33 Views
Last Modified: 2016-06-15
I need to compare records between to seperate database tables in two different databases.
Example:
Database A table1                                                 Database B table2
coulmn id                                                               column id
112233                                                                    112233
null                                                                          445566
778899                                                                     null
123456                                                                     123456

The quiery would return somthing like this:
Column ID Table 1 and Table 2         Is there an ID for both table 1 and 2       Is tere a id for table1    is there a in for table2
112233                                                            yes                                                                      yes                          yes
null                                                                   no                                                                      no                             yes
null                                                                   yes                                                        no                                                                  
123456                                                                 yes                                                                 yes                                 yes
0
Comment
Question by:newjeep19
5 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40415910
You need a "FULL OUTER JOIN":
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 1000 total points
ID: 40415914
Try this:

With myTable as
(Select ID
from DatabaseA.dbo.table1
UNION
Select ID
from DatabaseB.dbo.table2
)

Select M.ID, IIF(T.ID IS NULL OR U.ID IS NULL,'no','yes') as IDT1And2, 
IIF(T.ID IS NULL,'no','yes') as IDTable1,
IIF(U.ID IS NULL,'no','yes') as IDTable2
from myTable M
LEFT JOIN DatabaseA.dbo.table1 T
ON M.ID = T.ID
LEFT JOIN DatabaseB.dbo.table2 U
ON M.ID = U.ID

Open in new window

0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 40415917
SELECT
    ISNULL(t1.column_id, t2.column_id) AS column_id,
    CASE WHEN t1.column_id IS NULL OR t2.column_id IS NULL THEN 'No' ELSE 'Yes' END AS [ID for Table1 and Table2],
    CASE WHEN t1.column_id IS NULL THEN 'No' ELSE 'Yes' END AS [ID for Table1],
    CASE WHEN t2.column_id IS NULL THEN 'No' ELSE 'Yes' END AS [ID for Table2]
FROM dbA.table1 t1
FULL OUTER JOIN dbB.table2 t2 ON
    t2.column_id = t1.column_id


SELECT
    ISNULL(t1.column_id, t2.column_id) AS column_id,
    CASE WHEN t1.column_id IS NULL OR t2.column_id IS NULL THEN 'No' ELSE 'Yes' END AS [ID for Table1 and Table2],
    CASE WHEN t1.column_id IS NULL THEN 'No' ELSE 'Yes' END AS [ID for Table1],
    CASE WHEN t2.column_id IS NULL THEN 'No' ELSE 'Yes' END AS [ID for Table2]
FROM (
    SELECT '112233' AS column_id UNION ALL
    SELECT '778899' UNION ALL
    SELECT '123456'
) AS t1
FULL OUTER JOIN (
    SELECT '112233' AS column_id UNION ALL
    SELECT '445566' UNION ALL
    SELECT '123456'
) AS t2 ON t2.column_id = t1.column_id
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40418803
Please note that IIF() exists in SQL Server 2012 and 2014 only

Personally I would always use SQL standard CASE expressions in preference to the MS centric IIF() syntax.
e.g.

IIF(T.ID IS NULL OR U.ID IS NULL,'no','yes') as IDT1And2

CASE WHEN T.ID IS NULL OR U.ID IS NULL THEN 'no' ELSE 'yes' END as IDT1And2
0
 

Author Comment

by:newjeep19
ID: 40547432
fixed
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question