CODEWITHSUNDEEP
sqlms-accessvba
Johnny Bigoode
Johnny Bigoode

Reputation: 591

How can I change this VBA code into a SQL Query using JOIN?

It's a simple question, I'm getting started with SQL and I'm having a lot of problems with it.

I have TBL_SALES and TBL_CUSTOMERS. Both can be JOINed by the field CUSTOMER_ID. A customer may have a CUSTOMER_ID and have no entry on TBL_CUSTOMERS

I would like to FITER all entries on TBL_SALES that don't have an entry on TBL_CUSTOMERS.

Which I think it's the opposite of 

SELECT UNIQUE TBL_SALES.CUSTOMER_ID, TBL_CUSTOMERS.CUSTOMER_ID
FROM TBL_SALES, TBL_CUSTOMERS
WHERE TBL_SALES.CUSTOMER_ID = TBL_CUSTOMERS.CUSTOMER_ID

Well... I can do that with a VBA code using SEEK and NOMATCH. But I know it's not productive. And if I try to use this code, it doesn't work:

SELECT UNIQUE TBL_SALES.CUSTOMER_ID, TBL_CUSTOMERS.CUSTOMER_ID
FROM TBL_SALES, TBL_CUSTOMERS
WHERE TBL_SALES.CUSTOMER_ID <> TBL_CUSTOMERS.CUSTOMER_ID

I realized that there must be a 'SEEK' command in SQL but I'm pretty sure that is a smarter way to run this query.

Upvotes: 1

Views: 414

Answers (2)

Simon
Simon

Reputation: 6152

@Maarten van Leunen has just answered with pretty much exactly what I was going to put so I won't repeat it again. I did have one point that I wanted to make though: Can you really have orders with no related customer? I'm not sure if this a valid use case for you (seems unlikely) but if not you need to investigate the integrity of your database and start enforcing some foreign key relationships. i.e. You cannot save an order record without a link to a valid customer record.

Upvotes: 2

Maarten van Leunen
Maarten van Leunen

Reputation: 452

Try

SELECT *
FROM TBL_SALES
WHERE NOT EXISTS
(SELECT * FROM TBL_CUSTOMERS
WHERE TBL_CUSTOMERS.CUSTOMER_ID = TBL_SALES.CUSTOMER_ID)

It's also possible to use a LEFT JOIN, which selects all records on "the left" regardless wether or not there are existing records "on the right".

SELECT *
FROM TBL_SALES LEFT JOIN TBL_CUSTOMERS
ON TBL_SALES.CUSTOMER_ID = TBL_CUSTOMERS.CUSTOMER_ID
WHERE TBL_CUSTOMERS.CUSTOMER_ID IS NULL

Upvotes: 5

Related Questions