SQL: Compare two tables

Question

Hi i have this scenario and i am struck here.

I have two tables table A and table B which has one primary key in common (panelist_id) and for each panelist_id there are many entity_ids.

I want to check whether for each panelist_id and same entity_ids are present in both the table or not.

Note: There are many panelist_id's and for each panelist_ids there are many entity_ids..

Please help me out.

What RDBMS are you using? Also can you give us a table definition for table A and B. Also an example of your data and what result you expect will be helpful too. — twoleggedhorse, Jan 08 '13 at 09:34
I have tried the below query, but its not giving the appropriate results. SELECT COUNT(*) AS MISMATCHED_COUNT FROM FNDS_WRK_TOP_ENTITIES_VISIT_DAILY TEV JOIN VISITOR_IND_CHECK_DAILY TT on TEV.SOURCE_PANELIST_ID = TT.PANELIST_ID AND TEV.ENTITY_ID <> TT.ENTITY_ID — Mohit Bagadia, Jan 08 '13 at 09:34
use sqldbx for this to compare two tables , in that there is visual diff that will do this. — Trikaldarshiii, Jan 08 '13 at 09:32

score 2 · Answer 1 · answered Jan 08 '13 at 09:36

To select the panelist_id/entity_id tuples which are only available in one of the tables, you try do something like:

select panelist_id, entity_id from
(
  select panelist_id, entity_id from a
  union all
  select panelist_id, entity_id from b
)
group by panelist_id, entity_id
having count(*) = 1;

score 0 · Answer 2 · answered Jan 08 '13 at 09:34

0

Select A.panelist_id from tableA as A inner join tableB as B
on A.panelist_id=B.panelist_id
GROUP BY A.panelist_id
HAVING COUNT(DISTINCT A.entity_id)=COUNT(DISTINCT B.entity_id)

answered Jan 08 '13 at 09:34

Madhivanan

13,470
1
24
29

GarethD · Answer 3 · 2013-01-08T09:57:22.907

I can't work out exactly what you are asking, if you just want to know for each Entity_ID/Panelist_ID tuple if it exists in one or both tables then this will work:

SELECT  Panelist_ID, 
        Entity_ID,
        CASE WHEN COUNT(A) > 0 AND COUNT(B) > 0 THEN 'Both Tables'
            WHEN COUNT(B) = 0 THEN 'Table A'
            WHEN COUNT(A) = 0 THEN 'Table B'
        END AS Status
FROM    (   SELECT  Panelist_ID, Entity_ID, 1 AS A, NULL AS B
            FROM    A
            UNION ALL
            SELECT  Panelist_ID, Entity_ID, NULL AS A, 1 AS B
            FROM    B
        ) T
GROUP BY Panelist_ID, Entity_ID;

If you want to know the panelist_IDs where the tuples in each table are the same, with no tuples existing in one table that don't exist in the other you will need to use this:

SELECT  *
FROM    A
        FULL JOIN B
            ON A.Panelist_ID = B.Panelist_ID
            AND A.Entity_ID = B.Entity_ID
WHERE   A.Entity_ID IS NOT NULL
AND     B.Entity_ID IS NOT NULL;

Or if you wanted to know the tuples that exist in one table and not the other you could use:

SELECT  *
FROM    A
        FULL JOIN B
            ON A.Panelist_ID = B.Panelist_ID
            AND A.Entity_ID = B.Entity_ID
WHERE   A.Entity_ID IS NULL
OR      B.Entity_ID IS NULL;

EDIT

Based on the comment regarding only needing to validate that a temp table is the same as a permanent table you can use EXISTS:

SELECT  *
FROM    PermanentTable p
WHERE   NOT EXISTS 
        (   SELECT  1
            FROM    TempTable t
            WHERE   p.Panelist_ID = t.Panelist_ID
            AND     p.Entity_ID = t.Entity_ID
            ... More conditions to help find if entries are missing from temp table
        )

The detailed scenario goes like this. I have one temporary table which i have created using lot many join conditions and i have another permanent table which is the output table of some process. Now i need to check whether the data populated in the temporary table and the output table matches. So i have primary key as panelist id and for each panelist_id there are some thousands of entity_ids. I checked that panelist_ids are same in both the tables. Now i need to check whether for each panelist_id, same entity_ids are getting populated or not. — Mohit Bagadia, Jan 08 '13 at 09:52

SQL: Compare two tables

3 Answers3