joins with "could be blank" conditions Question is most  probably stupid but I am stuck on this and cant think anymore(Pissed Off).

I have these tables->  employee(has a column foreign to location),location and location_flag. And I need to select employee that are related to locations that have some specific flags on (like provides holidays, provides healthcare).

Suppose, a location(1) provides healthcare and holidays(having 'YES' for location_flagS) then I can simply check if they are 'YES'. So far so good?

Here's the complication:

What to do if the location have no track for some flags, Then joins will be failed.

My query will be:

select * 
from employee 
inner join location 
  on employee.locationId=location.id 
inner join location_flag lf 
  on lf.locationId=location.id 
inner join location_flag lf2 
  on lf2.location_id=location.id 
where lf.value='YES' and lf.flag_id=1
  and lf2.value='YES'and lf2.flag_id=2


If one of the flags are simply don't exists, the result will be NULL. 

What I want to is to give me the employees of the location that if have flags must be 'YES'. How can I do this?
Try this:

select emp.*, loc.*    
from employee as emp
  inner join location as loc
    on emp.locationId = loc.id 
where not exists
      ( select *
        from location_flag as lf1 
        where lf1.locationId = loc.id
          and lf1.flag_id = 1
          and lf1.value &lt;&gt; 'YES' 
      )
  and not exists
      ( select *
        from location_flag as lf2 
        where lf2.locationId = loc.id
          and lf2.flag_id = 2
          and lf2.value &lt;&gt; 'YES' 
      ) ;