CODEWITHSUNDEEP
javasqlnetezza
Mohit Bagadia
Mohit Bagadia

Reputation: 33

SQL: Compare two tables

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.

Upvotes: 0

Views: 930

Answers (3)

GarethD
GarethD

Reputation: 69809

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
        )

Upvotes: 0

vc 74
vc 74

Reputation: 38179

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;

Upvotes: 2

Madhivanan
Madhivanan

Reputation: 13700

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)

Upvotes: 0

Related Questions