MySQL: Exclude ‘group by’ group if field A in any row has value B with “Natural Join”

Querying databases get a little tricky when you have a stack of records associated with another table, perhaps a master table of accounts, and you’re trying to filter out values that would exclude the results from another group of results.
In this example below, we are getting a list of organization IDs for all countries who DO NOT have any address in the country Uzbekistan. Not picking on you Uzbekistan, just using your country name as an example…

This kind of filtering gets tricky, unless it’s clearly known the database tables are optimized, joins can bring your server down, so test it on a dev site and be prepared to stop the process on the server. It’s almost always safer to pre-qualify the list then iterate through and sort, though this way is slower, obviously, it’s not prone to slowing down your database performance.

Also, according to MySQL, there are some other basic guidelines to consider when using natural joins:

– The associated tables have one or more pairs of identically named columns with the SAME DATA TYPE.
– Do not use ON clause in a natural join.

Aaron Belchamber has written 243 articles

Leave a Reply

Your email address will not be published. Required fields are marked *