In a persisted field, how do you return the number of occurrences of a column within a different table's column

Question

The following is required due to records being entered by 3rd parties in a web application.

Certain columns (such as Category) require validation including the one below. I have a table OtherTable with the allowed values.

I need to identify how many occurrences (ie: IF) there are of the current table's column's value in a different table's specified column. If there are no occurrences this results in a flagged error '1', if there are occurrences, then it results in no flagged error '0'.

If `Category` can be found in `OtherTable.ColumnA` then return 0 else 1

How can I do this please?

Answer 1

select mt.*,IFNULL(cat_count.ct,0) as Occurrences from MainTable mt 
left outer join (select ColumnA,count(*) as ct from OtherTable) cat_count 
on mt.Category=cat_count.ColumnA

Result:

mt.col1 | mt.col2 | Category | Occurrences 
###     | ###     | XXX      | 3
###     | ###     | YYY      | 0
###     | ###     | ZZZ      | 1