SQL - return differences between rows (in two different tables) but ONLY if row ID exists in both tables

ron_g

I have two tables that contain two potential differences which I'm trying to pick up on - rows that exist only in one of the tables, and rows that exist in both (sharing a common ID) but having different values for one of the columns (columns are exactly the same in both tables).

CREATE TABLE "MyTable1" (ID INTEGER, FIRST_NAME TEXT, DOB DATE);
INSERT INTO MyTable1 VALUES (1, "Tom", "01-02-18");
INSERT INTO MyTable1 VALUES (2, "Dick", "02-02-18");
INSERT INTO MyTable1 VALUES (3, "Larry", "03-02-18");
INSERT INTO MyTable1 VALUES (4, "Jebroni", "04-02-18");

CREATE TABLE "MyTable2" (ID INTEGER, FIRST_NAME TEXT, DOB DATE);
INSERT INTO MyTable2 VALUES (1, "Tom", "01-02-18");
INSERT INTO MyTable2 VALUES (2, "Dick", "02-02-18");
INSERT INTO MyTable2 VALUES (3, "Barry", "03-02-18");

I can return IDs in MyTable1 not present in MyTable2:

SELECT MyTable1.*
FROM MyTable1
WHERE MyTable1.ID NOT IN (SELECT MyTable2.ID FROM MyTable2)

Returns what I'm after:

ID      FIRST_NAME      DOB
"4"     "Jebroni"       "04-02-18"

For the second part I want to compare values of each column for rows sharing a common ID.

SELECT 'TABLE1' AS SRC, MyTable1.*
FROM (
        SELECT * FROM MyTable1
        EXCEPT
        SELECT * FROM MyTable2
    ) AS MyTable1
UNION ALL
SELECT 'TABLE2' AS SRC, MyTable2.*
FROM (
        SELECT * FROM MyTable2
        EXCEPT
        SELECT * FROM MyTable1
    ) AS MyTable2

This returns more than what I'm after - rows that exist in one table and not the other:

SRC         ID      FIRST_NAME  DOB
"TABLE1"    "3"     "Larry"     "03-02-18"
"TABLE1"    "4"     "Jebroni"   "04-02-18"
"TABLE2"    "3"     "Barry"     "03-02-18"

How should I tweak my last query so that the result is instead:

SRC         ID      FIRST_NAME  DOB
"TABLE1"    "3"     "Larry"     "03-02-18"
"TABLE2"    "3"     "Barry"     "03-02-18"

I.e. restrict what's returns on the basis of the ID being present in both tables?

CL.

Restrict the first set of rows to those with a matching ID in the other table:

SELECT 'TABLE1' AS SRC, *
FROM (
        SELECT * FROM MyTable1 WHERE ID IN (SELECT ID FROM MyTable2)
        EXCEPT                 -------------------------------------
        SELECT * FROM MyTable2
    )
UNION ALL
SELECT 'TABLE2' AS SRC, *
FROM (
        SELECT * FROM MyTable2 WHERE ID IN (SELECT ID FROM MyTable1)
        EXCEPT                 -------------------------------------
        SELECT * FROM MyTable1
    );

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

sql query to return differences between two tables

Rows from different tables in one row sql

How to merge rows of two tables when there is no relation between the tables in sql

Tsrange - Subtracting the differences between two tables based on an id field

SQL Property differences between tables

Differences between two tables based on multiple columns in SQL

Tracking differences between two SQL tables (added, removed, changed)

Adding rows of stats from two different tables to make a total row. SQL

SQL EXISTS returns all rows, more than two tables

sql return json object by status from two tables with different number of rows

SQL SELECT only rows having MAX value of a column from two different tables

Joining two tables, multiple rows into a single row different columns

SQL - what's the difference between a JOIN and comparing two rows from different tables?

SQL select rows from two different tables with different column names

Join two tables in MSSQL and only return rows with a certain value in a field

Aggregated row count differences between tables

loop rows with two different tables

Join two tables and return only one row from second table

SQL: join on foreign key between two tables and return only count of items; not actual items

Joining and combining two tables that have the same ID for different rows

Combine 2 rows of different tables into one row in SQL?

Compare differences between two tables using Javascript

EFCore Getting Differences between two tables

SQL Select data from two tables, one row -> multiple rows

How to get different rows from two tables in SQL Server

Combine two tables which different number of rows in SQL server

Left Join to find difference between two tables with different id

Display two different tables by id

A sql query to create multiple rows in different tables using inserted id