Removing Items from a table that have no data in another table

[taholmes160]

HI Folks:

I am working on a medium size database (72,000+ records) in the particular table I am listing there are over 11,000 different commodities listed, as you can see in the attached pic, some have companies linked, others have no companies linked. I would like to find all the commodities that have no company linked and delete them. I can do it by hand, but its a bit tedious, so I am hoping for a "automated" solution. The first image below is a listing of the commodities table, the second picture shows the relationships that are established in the database. The data from this database will be exported as Excel files for use in another application.

[orange]

A quick question -- How and Why are the commodities with no associated Companies did these records get added??
What you ask can probably be done, but you should have a "business rationale" for deleting any records.

[taholmes160]

This database has not had data maintenance in a long time, and what has been done was very poorly done, so in many cases, there were several commodities that were the same thing but with different spelling -- for example

agricultural equipment
Agricultural Equipment
ag eq
ag equip
Ag Imp
ag implements
etc etc etc

im trying to get things cleaned up and working

TIM

[orange]

I think many here can relate to your situation. Something you may consider is to use a standard, authoritative table to identify your commodities. The idea is to have uniquely identified commodities, and to use their unique identifier in related tables, and making that commodities table a lookup table. This approach can standardize and streamline your maintenance - and reduce variations in spelling for the same commodity.
Good luck with your project.

[taholmes160]

I think many here can relate to your situation. Something you may consider is to use a standard, authoritative table to identify your commodities. The idea is to have uniquely identified commodities, and to use their unique identifier in related tables, and making that commodities table a lookup table. This approach can standardize and streamline your maintenance - and reduce variations in spelling for the same commodity.
Good luck with your project.

Hey Orange, thanks for the advice, thats exactly what I am working on doing, trying to get the commodities table cleaned up -- so far, Im doing it by hand and using update queries -- there are about 10000 commodities, and im up into the 200's I was able to select all of the ones with null companies, but I cannot get them to delete, so I'll keep working by hand -- theres no timeline on this project

Thanks a bunch

TIM

[June7]

Post query SQL for selecting commodities.

Delete action might be:

DELETE FROM tblCommodities WHERE CommodityID IN (SELECT CommodityID FROM tblCommodities LEFT JOIN tblCompanies ON tblCommodities.CommodityID=tblCompanies.CommodityI D WHERE CompanyID Is Null);

[isladogs]

Or a simpler version of the same idea with no subquery

DELETE DISTINCTROW tblCommodities.* 
FROM tblCompanies RIGHT JOIN tblCommodities ON tblCompanies.CommodityID = tblCommodities.CommodityID
WHERE tblCompanies.ConCompany Is Null;

Once you have deleted all orphaned records, you should establish referential integrity between tables so that no orphans will occur in future

[taholmes160]

Post query SQL for selecting commodities.

Delete action might be:

DELETE FROM tblCommodities WHERE CommodityID IN (SELECT CommodityID FROM tblCommodities LEFT JOIN tblCompanies ON tblCommodities.CommodityID=tblCompanies.CommodityI D WHERE CompanyID Is Null);

Heres the code for the current select query --

SELECT TblCommodities.CommodityID, TblCommodities.Commodity
FROM TblCommodities LEFT JOIN TblCompanies ON TblCommodities.[CommodityID] = TblCompanies.[CommodityID]
WHERE (((TblCompanies.CommodityID) Is Null));


Im headed off to work momentarially, so I'll give these code options a try when I get a few minutes later today or tomorrow - THANK YOU!!!!

TIM

[isladogs]

Your select query is checking for unmatched CommodityID. Although that may be useful, it isn't what you asked for originally - you said you wanted commodityID values in both tables wth no ConCompany record - in other words where the Subdatasheet is blank

[taholmes160]

Your select query is checking for unmatched CommodityID. Although that may be useful, it isn't what you asked for originally - you said you wanted commodityID values in both tables wth no ConCompany record - in other words where the Subdatasheet is blank

Yep, you are right, I tried using the query builder and looks like it backfired -- SQL is not my strong suite!

Thanks for the help

TIM

[isladogs]

Have you tried the code I suggested?

[taholmes160]

Thanks guys, that worked -- cutting my workload down considerably -- ive gotten 500 of the now 9000 records done -- I'll keep working on it

TIM

[isladogs]

Glad to see its working for you but I'm slightly confused.
If you're using my code, it should delete all the unwanted records at once - not just 500 of them

[taholmes160]

Have you tried the code I suggested?

Yep, Sure did -- worked a treat -- thanks

TIM

[taholmes160]

Hey colin, I miss spoke, it deleted all 1700+ of the unwanted records, now I am working through the remaining 9000+ of the remaining records. Cleaning them up, consolidating records etc. I keep re-using the sql after I fix a bunch to get rid of the newly empty ones -- its a bit time consuming, but thankfully theres no real rush, and hopefully, I'll get things done in the next couple weeks

TIM