MS SQL Server 2008
I've got two tables Company and Person.
The Table Person includes the column ReportA and ReportB
Persons are linked to their Company's with the field: person.comp_id
So each Company has one or more Persons linked.
I would like to select a list of all company's where none of the persons has the report "xyz" neither in the column ReportA and ReportB
Some Company's have persons that uses "xyz" others don't use them. I need all the company's where none of the linked persons uses the report "xyz".
This was my "Solution", but this will also give you company's where one ore more Persons use the Report "xyz".
from Person inner join Company on person.Id = company.Id
where (reportA not like '%xyz%' or reportA is null)
and (reportb not like '%xyz%' or reportb is null)