CODEWITHSUNDEEP
sql
JoSav
JoSav

Reputation: 247

Differences between 3 table

I want to compare 3 tables in a local database.

My problem is that i need to know which tables have the same data and which are missing data.

Example: 

Table A:
IpAddress    |HostName
10.10.01.10  | something
255.255.255.1| something else

Table B:
IpAddress    |HostName
10.10.01.10  |something

Table C:
IpAddress    |HostName
255.255.255.1| something else
1.1.1.1      | blabla

And I need to have a table that show me the data like that

IpAddress\HostName\TableA\TableB\TableC
10.10.01.10\something\1\1\0
255.255.255.1\something else\1\0\1
1.1.1.1\blabla\0\0\1

Upvotes: 1

Views: 88

Answers (2)

mtwaddell
mtwaddell

Reputation: 189

Here's one way to accomplish what you're asking

SELECT ipaddress||'\'||
       hostname||'\'||
       SUM(tabA)||'\'||
       SUM(tabB)||'\'||
       SUM(tabC)
  FROM (SELECT ipaddress, 
               hostname, 
               1 tabA, 
               0 tabB, 
               0 tabC 
          FROM tableA
        UNION ALL
        SELECT ipaddress, 
               hostname, 
               0 tabA, 
               1 tabB, 
               0 tabC 
          FROM tableB
        UNION ALL
        SELECT ipaddress, 
               hostname, 
               0 tabA, 
               0 tabB, 
               1 tabC 
          FROM tableC
       )
 GROUP BY ipaddress,
          hostname

I don't know what database you're working with, but this one will work with Oracle and I'm sure with minor modifications it would work with others.

Upvotes: 0

Taryn
Taryn

Reputation: 247680

You can use a UNION ALL to get the data from the three tables in a subquery and then use an aggregate function with a CASE expression to count the number of times the ipaddress and hostname appear in each table:

select ipaddress, HostName,
    SUM(case when tbl = 'a' then 1 else 0 end) tablea, 
    SUM(case when tbl = 'b' then 1 else 0 end) tableb,
    SUM(case when tbl = 'c' then 1 else 0 end) tablec
from
(
    select ipaddress, HostName, 'a' tbl
    from tablea
    union all
    select ipaddress, HostName, 'b' tbl
    from tableb
    union all
    select ipaddress, HostName, 'c' tbl
    from tablec
) d
group by ipaddress, HostName;

See SQL Fiddle with Demo

Upvotes: 5

Related Questions