T-SQL Query comparing Member counts between 2 tables-CodePudding

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?

CodePudding user response：

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 )

CodePudding user response：

SELECT MemberID
FROM Table1
WHERE MemberID NOT IN (SELECT MemberID FROM Table2)

CodePudding user response：

We can use a LEFT OUTER JOIN to compare the two tables and determine which MemberIDs were sent by us but not returned by the vendor.

First, we will need to join the two tables in the MemberID column. We can do this by using the following query:

SELECT t1.MemberID, t1.FirstName, t1.LastName, t2.FirstName, t2.LastName
FROM TABLE1 t1 
LEFT OUTER JOIN TABLE2 t2 
ON t1.MemberID = t2.MemberID

This query will return all the MemberIDs from TABLE1, along with the corresponding FirstName and LastName from both TABLE1 and TABLE2.

Now, we can filter the result set to only include the MemberIDs from TABLE1 that do not have a corresponding record in TABLE2. To do this, we can add the following WHERE clause to the query above:

WHERE t2.MemberID IS NULL

This query will return only the MemberIDs from TABLE1 that do not have a corresponding record in TABLE2.

CodePudding user response：

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.)