CODEWITHSUNDEEP
sqlms-access
Chuck0185
Chuck0185

Reputation: 531

Using WHERE NOT EXISTS in MS Access SQL Query

I have a query that matches a field from a query to another field from a table. Here is the query:

SELECT DISTINCT CarriersToSend.Carrier, [Dual Year Carrier Report].TPA_CARRIER
FROM [Dual Year Carrier Report] INNER JOIN CarriersToSend ON [Dual Year Carrier Report].TPA_CARRIER = CarriersToSend.Carrier;

It returns the values that match in the Carrier field of the CarriersToSend query and the TPA_CARRIER field of [Dual Year Carrier Report].

I then need to return the values that are in the Carrier field of the CarriersToSend query that do not appear in the above query. I think it would be something like a WHERE NOT EXISTS QUERY. I put one together below, but it is not returning anything and I cant figure out why. Any help would be much appreciated. Thank you!

SELECT DISTINCT EE_First, EE_LAST
FROM [Dual Year Carrier Report]
WHERE NOT EXISTS (
SELECT DISTINCT CarriersToSend.Carrier, [Dual Year Carrier Report].TPA_CARRIER
FROM [Dual Year Carrier Report] INNER JOIN CarriersToSend ON [Dual Year Carrier Report].TPA_CARRIER = CarriersToSend.Carrier;)

Upvotes: 2

Views: 22498

Answers (2)

D-Shih
D-Shih

Reputation: 46229

Your NOT EXISTS with subquery didn't connect with the main query, so that didn't return any result.

You can try this.

SELECT DISTINCT EE_First, EE_LAST
FROM [Dual Year Carrier Report] t1
WHERE NOT EXISTS 
(
    SELECT 1
    FROM CarriersToSend t2
    WHERE t1.TPA_CARRIER = t2.Carrier
)

Upvotes: 2

Rene
Rene

Reputation: 1093

Your semi colon should come after the closing parenthesis. Apart from that, your query will logically never return any records. That's because your subquery returns a result and then you are trying to return a result that doesn't match the result of the subquery. This will logically result in a false condition. Try this instead:

SELECT DISTINCT EE_First, EE_LAST
FROM [Dual Year Carrier Report]
WHERE NOT EXISTS 
(
SELECT '1'
FROM CarriersToSend INNER JOIN [Dual Year Carrier Report] ON 
CarriersToSend.Carrier = [Dual Year Carrier Report].TPA_CARRIER
);

Upvotes: 3

Related Questions