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?