SQL Joins ?

[SpookiePower]

I'm not sure how to do this, but I guess that I need
to use some kind of join.

Here is my question -

I have a table "taCustomer" with customerdata, where one field is the
customernumber.
I have another table "taItems" holding data about things the customers
owns, and
a customernumber.

A while ago I deleted some customers in taCustomer, but because of an
error, the
customers belongings in the other table taItems was not deleted.

No I need to find all the customernumbers that is in the taItems-
table,
but NOT in the taCustomer-table, so I can delete them.

How can I do this ? I'm not sure what SQL join to use.

 

[Marshall Barton]

I'm not sure how to do this, but I guess that I need
to use some kind of join.

Here is my question -

I have a table "taCustomer" with customerdata, where one field is the
customernumber.
I have another table "taItems" holding data about things the customers
owns, and
a customernumber.

A while ago I deleted some customers in taCustomer, but because of an
error, the
customers belongings in the other table taItems was not deleted.

No I need to find all the customernumbers that is in the taItems-
table,
but NOT in the taCustomer-table, so I can delete them.

How can I do this ? I'm not sure what SQL join to use.

Try creating a new select query using the Find Unmatched
query wizard. Check to make sure it is selecting the
correct records. When it finally does select the correct
set of records, make a backup of your table's database and
then use the query menu to change it to a Delete query.

 

[John W. Vinson]

No I need to find all the customernumbers that is in the taItems-
table,
but NOT in the taCustomer-table, so I can delete them.

You can get some help from Access: create a new query using the "Unmatched
Query Wizard".

How can I do this ? I'm not sure what SQL join to use.

To roll your own, without the wizard, you need a "frustrated outer join"
query. Create a Query joining taCustomers to taItems by CustomerID. Use a Left
Outer Join - probably option 3, "show all items in taItems and matching
records in taCustomers ". Put a criterion on taCustomers CustomerID of

IS NULL

The left join by itself will show you all the records in taItems, with the
customer ID from taCustomers if there is one and NULL if there isn't; the
criterion will just find the oddballs.

 

[SpookiePower]

Try creating a new select query using the Find Unmatched
query wizard.  Check to make sure it is selecting the
correct records.  When it finally does select the correct
set of records, make a backup of your table's database and
then use the query menu to change it to a Delete query.

I did not know about this "Find Unmatched
query wizard". I'll try to look at it. Thanks.

 

[SpookiePower]

You can get some help from Access: create a new query using the "Unmatched
Query Wizard".


To roll your own, without the wizard, you need a "frustrated outer join"
query. Create a Query joining taCustomers to taItems by CustomerID. Use a Left
Outer Join - probably option 3, "show all items in taItems and matching
records in taCustomers ". Put a criterion on taCustomers CustomerID of

IS NULL

The left join by itself will show you all the records in taItems, with the
customer ID from taCustomers if there is one and NULL if there isn't; the
criterion will just find the oddballs.

Thanks