-2

TABLE 1: Data sent to vendor

| MemberID | FirstName | LastName | Etc |
| :------: | :-------: | :------: | :-: |
| 1        | John      | Smith    | Etc |
| 2        | Jane      | Doe      | Etc |
| 3        | Dan       | Laren    | Etc |

TABLE 2: Data returned from vendor

| MemberID | FirstName | LastName | Etc |
| :------: | :-------: | :------: | :-: |
| 1        | John      | Smith    | Etc |
| 2        | Jane      | Doe      | Etc |
| 3        | Dan       | Laren    | Etc |

We send data to a vendor which is used for their matching algorithm and they return the data with new information. The members are matched with a MemberID data element. How would I write a query which shows me which MemberIDs we sent to the vendor but the vendor didn't return?

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
Austin
  • 153
  • 2
  • 11

3 Answers3

3

NOT EXITS would be my first choice here.

Example

SELECT *
FROM   Table1 A
WHERE  NOT EXISTS (SELECT 1
                   FROM   Table2 B
                   WHERE  A.MemberID = B.MemberID )
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
1
SELECT MemberID
FROM Table1
WHERE MemberID NOT IN (SELECT MemberID FROM Table2)
user580950
  • 3,558
  • 12
  • 49
  • 94
  • 2
    NOT IN could get expensive. Perhaps you should consider NOT EXISTS https://stackoverflow.com/questions/173041/not-in-vs-not-exists – John Cappelletti Feb 03 '23 at 20:17
0

Using EXCEPT is one option.

SELECT sent.[MemberID] FROM Tbl1_SentToVendor       sent
EXCEPT
SELECT recv.[MemberID] FROM Tbl2_ReturnedFromVendor recv

This is just on MemberID, but the "EXCEPT" syntax can also support additional columns (e.g., in case you want to filter out data that may be the same as what you already have.)

Sean
  • 91
  • 4