1 Reply Latest reply: Aug 16, 2013 6:49 AM by dhalek RSS

    Problem with conditional query.

    1013527

      Hello everybody,

       

      I have two tables employee and phone. In phone table there are two type phone no W1 and M that is PHONE_TYPE_CODE.

      Now i want records of those employee who does not have any phone number not W1 and M type.

      There are 2 records in phone table for each employee with PHONE_TYPE_CODE W1 and M. For any employee if W1 And M type phone_number is null i need those record.

      Can you please suggest me what condition i use for that.

       

      PERSON_ID11NNUMBERNone
      ORG_ID2NNUMBERFrequency
      LOC_ID3NNUMBERFrequency
      FULL_NAME6NVARCHAR2 (250 Byte)Height Balanced
      FIRST_NAME7NVARCHAR2 (20 Byte)Height Balanced
      MIDDLE_NAME8YVARCHAR2 (60 Byte)None
      LAST_NAME9NVARCHAR2 (40 Byte)Height Balanced

       

       

      PERSON_ID1NNUMBERNone
      PHONE_TYPE_CODE2NVARCHAR2 (100 Byte)Frequency
      PHONE_NUMBER3YVARCHAR2 (60 Byte)Height Balanced

       

      The data in phone table. In result i don't want 1773,31603. I want just 1779. So can you please suggest me what condition i apply.

       

      PERSON_IDPHONE_TYPE_CODEPHONE_NUMBER
      1773W17037632888 x1035
      1773M
      1779W1
      1779M
      31603M
      31603W19027492700
        • 1. Re: Problem with conditional query.
          dhalek

          First, you are writing into the wrong forum as this one is just for questions on Oracle's SQL Developer tool.

          Having that said, you could do something like this:

          select
          person_id
          from
          phone
          where
          phone_number is null
          group by
          person_id
          having
          count(distinct phone_Type_code)=2;
          

           

          Hope that helps,

          dhalek