Extracting records that have no related records

[martakeithy]

Hi,
I have a query regarding a mailing list I have been asked to produce. My client has a database which contains names and addresses of customers interested in my client's training service. Some of these customers go on to register for a training course, some do not.
My client wants to send mailings to the customers that have not actually registered for a course.
There are three tables involved:1. client details (i.e. customer details)- featuring address etc... 2.registration and 3.course details.
All customers that have proceeded to register for a course will have one or more registrations on the registration table.
The customer details I need to extract are those with no attached registration records.
How is this done??
I look forward to hearing your suggestions.
Thank you in advance
Marta

 

[scriverb]

Here is a model for SQL to use to do what you ask. This SQL performs a left join be the Customers table and the Registration table matching on CustomerID. If the value of CustomerID in the Registration table is null then the Customers record is selected. You see if the Registration match is null means that there has never been a registration for this CustomerID. Now I chose CustomerID as a matching field. You will have to update the query with the accurate field name that can be used to match the records. Name is not a good field to use. You should have a field that uniquely identifies the Registration record with the Customer table.

Select *
FROM Customers as A LEFT JOIN Registrations as B ON A.CustomerID = B.CustomerID
WHERE (((B.CustomerID) Is Null));

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???