CODEWITHSUNDEEP
sqlreporting-servicesreportingservices-2005
PRNDL Development Studios
PRNDL Development Studios

Reputation: 3300

Comparing two datasets SQL SSRS 2005

I have two datasets on two seperate servers. They both pull one column of information each.

I would like to build a report showing the values of the rows that only appear in one of the datasets.

From what I have read, it seems I would like to do this on the SQL side, not the reporting side; I am not sure how to do that.

If someone could shed some light on how that is possible, I would really appreciate it.

Upvotes: 1

Views: 1708

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

If you want rows that appear in exactly one data set and you have a matching key on each table, then you can use a full outer join:

select *
from table1 t1 full outer join
     table2 t2
     on t1.key = t2.key
where t1.key is null and t2.key is not null or
      t1.key is not null and t2.key is null

The where condition chooses the rows where exactly one match.

The problem with this query, though, is that you get lots of columns with nulls. One way to fix this is by going through the columns one by one in the SELECT clause.

select coalesce(t1.key, t2.key) as key, . . .

Another way to solve this problem is to use a union with a window function. This version brings together all the rows and counts the number of times that key appears:

select t.*
from (select t.*, count(*) over (partition by key) as keycnt
      from ((select 'Table1' as which, t.*
             from table1 t
            ) union all
            (select 'Table2' as which, t.*
             from table2 t
            )
           ) t
     ) t
where keycnt = 1

This has the additional column specifying which table the value comes from. It also has an extra column, keycnt, with the value 1. If you have a composite key, you would just replace with the list of columns specifying a match between the two tables.

Upvotes: 1

Void Ray
Void Ray

Reputation: 10219

You can also use a left join:

select a.* from tableA a
    left join tableB b
    on a.PrimaryKey = b.ForeignKey
where b.ForeignKey is null

This query will return all records from tableA that do not have corresponding records in tableB.

Upvotes: 1

Peter Majeed
Peter Majeed

Reputation: 5362

You can use the NOT EXISTS clause to get the differences between the two tables.

SELECT
    Column
FROM
    DatabaseName.SchemaName.Table1
WHERE
    NOT EXISTS
    (
        SELECT
            Column
        FROM
            LinkedServerName.DatabaseName.SchemaName.Table2
        WHERE
            Table1.Column = Table2.Column --looks at equalities, and doesn't
                                          --include them because of the
                                          --NOT EXISTS clause
    )

This will show the rows in Table1 that don't appear in Table2. You can reverse the table names to find the rows in Table2 that don't appear in Table1.

Edit: Made an edit to show what the case would be in the event of linked servers. Also, if you wanted to see all of the rows that are not shared in both tables at the same time, you can try something as in the below.

SELECT
    Column, 'Table1' TableName
FROM
    DatabaseName.SchemaName.Table1
WHERE
    NOT EXISTS
    (
        SELECT
            Column
        FROM
            LinkedServerName.DatabaseName.SchemaName.Table2
        WHERE
            Table1.Column = Table2.Column --looks at equalities, and doesn't
                                          --include them because of the
                                          --NOT EXISTS clause
    )

UNION

SELECT
    Column, 'Table2' TableName
FROM
    LinkedServerName.DatabaseName.SchemaName.Table2
WHERE
    NOT EXISTS
    (
        SELECT
            Column
        FROM
            DatabaseName.SchemaName.Table1
        WHERE
            Table1.Column = Table2.Column
    )

Upvotes: 1

Related Questions