0

I know about the exists feature, but that does not help me with the given situation. What I want is:

Check if some row exists, and if exists then check for an another row if row exists.

Here's the sqlFiddle.

Description:

A location table:

| ID |   NAME | ADDRESS |
|----|--------|---------|
|  1 | India1 |  (null) |
|  2 | India2 |  (null) |
|  3 | India3 |  (null) |
|  4 | India4 |  (null) |
|  5 | India5 |  (null) |

Then a location_flag table:

| ID | LOCATION_ID | FLAG_ID | VALUE | PARENT_ID | DELETED |
|----|-------------|---------|-------|-----------|---------|
|  1 |           1 |       1 |   YES |    (null) |  (null) |
|  2 |           1 |       2 |   YES |    (null) |  (null) |
|  3 |           2 |       1 |   YES |    (null) |  (null) |
|  4 |           2 |       2 |    NO |    (null) |  (null) |

What I want is to get all locations except location with id=2.

In procedure, it'd be like:
if(locationWithFlag1='YES'){
 if(locationWithFlag2='YES'){
//make this row to be appear in result
}
}
else{
//make this row appear in result
}

Is it possible without bringing in plpgsql?

closed as unclear what you're asking by ypercubeᵀᴹ, dezso, RolandoMySQLDBA, Kin Shah, RLF Oct 9 '14 at 17:48

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    You need to provide a lot more details than that. Show us the table definition (as create table) show us some sample data (as insert into ...) and show us the expected output based on that sample data. Ideally create a sqlfiddle.com example – a_horse_with_no_name Oct 8 '14 at 7:48
  • @a_horse_with_no_name check this out. sqlfiddle.com/#!12/7a94a/3 – Sachin Verma Oct 8 '14 at 8:14
  • 1
    And what is the expected output? I don't really understand the "then I want to check for flag_id=2 and value='Yes'" part. – a_horse_with_no_name Oct 8 '14 at 8:17
  • I updated again – Sachin Verma Oct 8 '14 at 8:17
  • @SachinVerma This is similar to your previous question. Just use where exists (...) and exists (...) instead of not exists – ypercubeᵀᴹ Oct 8 '14 at 8:47
4
select l.* 
from location as l                             --- find all locations
where not exists                               --- where there isn't 
      ( select * 
        from location_flag as lf               --- a flag
        where lf.location_id = l.id 
          and lf.flag_id = 1                   --- with 1
          and lf.value = 'YES'                 --- and YES
          and not exists                       --- without 
              ( select *                      
                from location_flag as lf2      --- another flag
                where lf2.location_id = l.id 
                  and lf2.flag_id = 2          --- with 2
                  and lf2.value = 'YES'        --- and YES
              )
      ) ;

or:

select l.* 
from location as l 
where not exists 
      ( select * 
        from location_flag as lf
        where lf.location_id = l.id 
          and lf.flag_id = 1 
          and lf.value = 'YES'
      )
   or exists
      ( select * 
        from location_flag as lf2
        where lf2.location_id = l.id 
        and lf2.flag_id = 2 
        and lf2.value = 'YES'
      ) ;
  • you're to eager to help and I'm too lazy to even seek help. You're one of the last good human beings left on this planet. May the force be with you :) thanks. which one is faster btw? – Sachin Verma Oct 8 '14 at 10:18

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