I think some kind of self-join something might work here but this issue is driving me crazy.
I have a list of companies (Companies) and various tests have been performed on them (by another dept) - in my example, if the company name is similar to any other in the table (test1) it FAILS, and ifthe phone number similar (test2) it fails too.
I also have a 'Results' table which links matching companies into groups. So Apple and Apple Inc are deemed similar, and fail the test. The lookup table links them together (the design of this table is not set in stone and can be changed.) I will have many more tests eventually.
I want to be able to display a complete list of all my companies, show wether they pass or fail the tests, and if they fail, to be able to click the company name and see a list of companies which are linked on the fail.
Attached example should make a bit more sense, although my data model is down the wrong path (and doesnt work anyway!)
Im thinking some kind of self join on the Results table perhaps? Would love some advice.