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?

  • 2
    Why not use a LEFT JOIN on the location_flag table? – Taryn Oct 7 '14 at 10:37
  • I use but it returns dups. – Sachin Verma Oct 7 '14 at 10:38
  • 2
    I'd suggest creating a sql fiddle with your tables, sample data and then post the desired final result. – Taryn Oct 7 '14 at 10:39
  • If it return dups, you should be using LEFT JOIN, DISTINCT and ORDER BY clauses to avoid it. – Kamil Gosciminski Oct 7 '14 at 10:43

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 <> 'YES' 
  and not exists
      ( select *
        from location_flag as lf2 
        where lf2.locationId = loc.id
          and lf2.flag_id = 2
          and lf2.value <> 'YES' 
      ) ;
| improve this answer | |
  • Thanks for coming by. Its a super heavy query and exists should take of that. awesome – Sachin Verma Oct 7 '14 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.