Select all data from one table that does not exists in another table given 3 key columns

Bill

I would like to identify records that are in table #1 but not in table #2 in a situation where there are three composite keys to determine if a record is unique. The below code is as close as I get but the where clause needs work.

In this test case the results includes:

(2, 3, 2, null)
(4, 4, 1, null)
(7, 2, 2, null)
(8, 1, 2, null)
CREATE TABLE OldReports (
   REPORT_ID int,
   USER_ID int,
   CLIENT_ID int,
   MY_DATA varchar(100)
)

INSERT INTO OldReports
   (REPORT_ID, USER_ID, CLIENT_ID, MY_DATA)
VALUES
   (1, 1, 2, null),
   (6, 3, 3, null),
   (6, 4, 4, null),
   (5, 1, 2, null),
   (5, 1, 3, null),
   (7, 2, 1, null),
   (8, 1, 1, null)

CREATE TABLE NewReports (
   REPORT_ID int,
   USER_ID int,
   CLIENT_ID int,
   MY_DATA varchar(100)
)

INSERT INTO NewReports
   (REPORT_ID, USER_ID, CLIENT_ID, MY_DATA)
VALUES
   (1, 1, 2, null),
   (2, 3, 2, null),
   (4, 4, 1, null),
   (5, 1, 2, null),
   (5, 1, 3, null),
   (7, 2, 2, null),
   (8, 1, 2, null)

DROP TABLE IF EXISTS #ReportDifferences

SELECT DISTINCT
    REPORT_ID,
    USER_ID,
    CLIENT_ID
FROM NewReports n
WHERE (IF NOT EXISTS (
        SELECT * 
        FROM OldReports o 
        WHERE 
            n.REPORT_ID = o.REPORT_ID and
            n.USER_ID = o.USER_ID and
            n.CLIENT_ID = o.CLIENT_ID)
)

-- tell me what lives in NewReports but not in OldReports
SELECT * FROM #ReportDifferences

I attempted this solution (How to select all records from one table that do not exist in another table?) but on a large data set, the results are not mutually exclusive. Something like:

LEFT JOIN OldReports o ON u.CLIENT_ID = o.CLIENT_ID AND u.REPORT_ID = o.REPORT_ID AND u.USER_ID = o.USER_ID
WHERE
    u.CLIENT_ID is null
    AND u.REPORT_ID is null
    AND u.USER_ID is null
Dale K

I suggest using the EXCEPT operator

select *
from NewReports
except
select *
from OldReports;

Or if by data you mean multiple columns which aren't actually null, then you can join on the result of EXCEPT e.g.

select nr.*
from NewReports nr
join (
    select REPORT_ID, USER_ID, CLIENT_ID
    from NewReports
    except
    select REPORT_ID, USER_ID, CLIENT_ID
    from OldReports
) er on er.REPORT_ID = nr.REPORT_ID
    and er.USER_ID = nr.USER_ID
    and er.CLIENT_ID = nr.CLIENT_ID;

Returns

REPORT_ID USER_ID CLIENT_ID MY_DATA
2 3 2 null
4 4 1 null
7 2 2 null
8 1 2 null

https://dbfiddle.uk/tyk4Rlbl

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Select all columns from one table and 1 column from another

Select from one table where id (from another table) exists

SQL Select all columns from one table and Max value of another column on another table

Data from one table to select data columns from another table, using r

check all rows from one table exists on another table

MySQL Select all columns from one table and some from another table

SQL querying all date ranges from one table not between any date ranges of another for given foreign key

Select data from one table & then rename the columns based on another table in SQL server

Append columns from one data table to another data table

Select all from one table where some columns match another select

How to select all data from one table and records from another table matching data in first selection. All in one query

How to select items from a table based on one value if another value does not exists? (eloquent/sql)

Insert from another table, and update if key exists, possible in one query?

SELECT get values from one parent table given values from another parent table using junction table

Select foreign key from table where one of the column's value in all rows with same foreign key are greater than given value

C# EF if row exists in table one select it else select from another table

select row from one table where value exists from array from another table

how to join one column to all columns from another table

How to select all records from one table that do not exist in another table for certain condition in another table?

Select Join From 2 table wich one of the table has array value of key from another table

Select all columns from table where one field is duplicated

Query records in one table that exists in either of two columns in another table

SQL Query - select all from one table with matching records in another

I would like to sum all of the data in a specific table column, if the appropriate ID of that table exists in another one

How to get all columns from one table and one column from another table in SQLITE

How to get all columns from one table and only one column from another table with ID ? - MySql

Select data from one table with the conditions from another

If exists select column from another table

VBA Copying data from one table to another and rearranging columns