CODEWITHSUNDEEP
sqloracle-databaseplsql
user2017738
user2017738

Reputation: 1

comparing values in common columns of two diferrent tables and display the difference

I have two tables USER and USER_BAL. USER _ID is primary key of USER table and foreign key of USER_BAL table. I need to compare the USER_ID column of both table in order to make sure that any value in USER_BAL.USER_ID has a matching value in USER.USER_ID. I also need to output the left out ones.

Upvotes: 0

Views: 896

Answers (2)

user2001117
user2001117

Reputation: 3777

Try this :

This query give the userid which is exists into user table. 

SELECT USER_BAL.USER_ID 
FROM USER_BAL 
WHERE EXISTS (
                SELECT USER.USER_ID 
                FROM USER 
                WHERE USER.USER_ID = USER_BAL.USER_ID
             )

This query give the userid which is not exists into user table

SELECT USER_BAL.USER_ID 
FROM USER_BAL 
WHERE NOT EXISTS (
                    SELECT USER.USER_ID 
                    FROM USER 
                    WHERE USER.USER_ID = USER_BAL.USER_ID
                 )

Upvotes: 0

Dave K
Dave K

Reputation: 1995

The following will give you all the users with an entry in User_Bal but not in User:

SELECT DISTINCT User_Bal.UserID
From User_Bal
Where User_Bal.UserID NOT IN
(
   SELECT User.UserID
   FROM USER
)

Ivan's answer is a good starting point if you need to get UserIDs in User but not in User_Bal AND UserIDs in User_Bal but not in User.

However, the results will depend on your DB schema. Is User to User_Bal a one-to-one or one-to-many relationship?

If one-to-one then you should change your schema to make UserID both a FK and PK of the User_Bal table. If one-to-many then Ivan's answer will return multiple rows for the same UserID.

Upvotes: 1

Related Questions