4 Replies Latest reply on Jun 24, 2015 8:00 AM by markmevans

    Get value in different column




      I have the following 2 tables: people and address


      create table p_ppl

      (id number primary key,

      name varchar2(20));


      create table p_adr

      (id_address number primary key,

      id_ppl number,

      city varchar2(10),

      type_address varchar2(10),

      constraint fk_ppl foreign key ( id_ppl) references p_ppl(id),

      constraint  ck_type check (type_address in ('C', 'H', 'M', 'P')));


      The requirement is:


      Get the city for a certain people: if type_address = 'C' get the corresponding city; if no type_address = 'C' for that people, get the city for type_address = 'H'; if no type_address = 'H' then get any city existing in the p_adr table, for that people.


      I could not manage to find a solution to this; I tryied using pivot, but without getting the needed result.


      Could you please advise?


      Thank you!