It is not entirely clear from your screenshot what data you have in what tables and what the relationship between the data is. Please spell out in words what you want to accomplish so we may be able to help you better.
There is no single relationship that will give you the results in your screen shot, and I don't believe you will be able to get the results in that format.
Results for each of the conditions (Starts with, Is equal, Contains, Expecting) can be done with SQL function aliases. The first 3 are available out of the box, I believe. The last (Expecting) you will have to write yourself.
As to the format of the report, I think the easiest way to present the results would be to create a summary type report with counts of the results that satisfy each condition, then drill down to the list of results for each condition from there.