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

    Get value in different column

    user1688514

      Hi,

       

      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!