8 Replies Latest reply: Feb 24, 2013 4:02 PM by Solomon Yakobson RSS

    How can I get an ID with particular condition

    993017
      Hello,
      I try to write an sql statement but I can not unable to work out.I want to get GROUP_ID which include BLUE and GREEN but not BLACK colour.In this scenario my statement should return GROUP_ID like "2220052". 8311226 has GREEN and BLUE but also has BLACK. It should not be return. I searched many forums but I can not find anything( maybe I'm using wrong keywords)

      TEST TABLE
      GROUP_ID     COLOUR     KEY
      8311226          GREEN     12428183
      8311226          BLUE     12428183
      8311226          GREEN     35428017
      8311226          BLACK     5415875
      2220052          GREEN     12253085
      2220052          WHITE     12253085
      2220052          BLUE     84553085
      3029178          BLACK     54587856
      3029178          BLUE     54587856
      3029178          GREEN     67080752
      Any help appreciate,
      Regards,
        • 1. Re: How can I get an ID with particular condition
          Solomon Yakobson
          with t as (
                     select 8311226 group_id,'GREEN' color,12428183 key from dual union all
                     select 8311226,'BLUE',12428183 from dual union all
                     select 8311226,'GREEN',35428017 from dual union all
                     select 8311226,'BLACK',5415875 from dual union all
                     select 2220052,'GREEN',12253085 from dual union all
                     select 2220052,'WHITE',12253085 from dual union all
                     select 2220052,'BLUE',84553085 from dual union all
                     select 3029178,'BLACK',54587856 from dual union all
                     select 3029178,'BLUE',54587856 from dual union all
                     select 3029178,'GREEN',67080752 from dual
                    )
          select  group_id
            from  t
            group by group_id
            having sum(distinct case color
                                  when 'BLUE' then 1
                                  when 'GREEN' then 2
                                  when 'BLACK' then 4
                                end
                      ) = 3
          /
          
            GROUP_ID
          ----------
             2220052
          
          SQL>
          SY.
          • 2. Re: How can I get an ID with particular condition
            sb92075
            990014 wrote:
            Hello,
            I try to write an sql statement but I can not unable to work out.I want to get GROUP_ID which include BLUE and GREEN but not BLACK colour.In this scenario my statement should return GROUP_ID like "2220052". 8311226 has GREEN and BLUE but also has BLACK. It should not be return. I searched many forums but I can not find anything( maybe I'm using wrong keywords)

            TEST TABLE
            GROUP_ID     COLOUR     KEY
            8311226          GREEN     12428183
            8311226          BLUE     12428183
            8311226          GREEN     35428017
            8311226          BLACK     5415875
            2220052          GREEN     12253085
            2220052          WHITE     12253085
            2220052          BLUE     84553085
            3029178          BLACK     54587856
            3029178          BLUE     54587856
            3029178          GREEN     67080752
            Any help appreciate,
            Regards,
            SELECT GROUP_ID FROM TABLE_TEST
            WHERE COLOUR = 'BLUE' OR COLOUR = 'GREEN'
            MINUS
            SELECT GROUP_ID FROM TABLE_TEST
            WHERE COLOUR = 'BLACK';


            How do I ask a question on the forums?
            SQL and PL/SQL FAQ
            • 3. Re: How can I get an ID with particular condition
              Solomon Yakobson
              sb92075 wrote:
              SELECT GROUP_ID FROM TABLE_TEST
              WHERE COLOUR = 'BLUE' OR COLOUR = 'GREEN'
              MINUS
              SELECT GROUP_ID FROM TABLE_TEST
              WHERE COLOUR = 'BLACK';
              This will return group id with blue and no black no green and group id with green and no black no blue, while OP wants both blue & green and no black.

              SY.
              • 4. Re: How can I get an ID with particular condition
                stefan nebesnak
                select distinct group_id
                  from tab_tmp t
                 where t.group_id not in (select distinct t2.group_id
                                            from tab_tmp t2
                                           where t2.colour = 'BLACK');
                ----------------
                        GROUP_ID
                1     2220052
                -----
                Edited by: stefan nebesnak on Feb 24, 2013 9:02 AM

                In the TEST table there are the 'BLUE' and 'GREEN' values together in all cases, which confused me. ツ
                <tt>
                GROUP_ID     COLOUR     KEY
                <font color="#FF0000">--1--</font>
                8311226          <font style="background-color: #E7FA6B">GREEN</font>     12428183
                8311226          <font style="background-color: #D7CEFF">BLUE</font>     12428183
                8311226          <font style="background-color: #E7FA6B">GREEN</font>     35428017
                8311226          BLACK     5415875
                <font color="#FF0000">--2--</font>
                2220052          <font style="background-color: #E7FA6B">GREEN</font>     12253085
                2220052          WHITE     12253085
                2220052          <font style="background-color: #D7CEFF">BLUE</font>     84553085
                <font color="#FF0000">--3--</font>
                3029178          BLACK     54587856
                3029178          <font style="background-color: #D7CEFF">BLUE</font>     54587856
                3029178          <font style="background-color: #E7FA6B">GREEN</font>     67080752
                </tt>
                • 5. Re: How can I get an ID with particular condition
                  Solomon Yakobson
                  stefan nebesnak wrote:
                  select distinct group_id
                  from tab_tmp t
                  where t.group_id not in (select distinct t2.group_id
                  from tab_tmp t2
                  where t2.colour = 'BLACK');
                  ----------------
                       GROUP_ID
                  1     2220052
                  Your query will return group id with ANY colors but black, while OP wants both blue & green and no black.

                  SY.
                  • 6. Re: How can I get an ID with particular condition
                    Frank Kulash
                    Welcome to the forum!

                    Here's another way. It's a little more code, but perhaps easier to understand, debug and maintain:
                    SELECT       group_id
                    FROM       t
                    GROUP BY  group_id
                    HAVING       2 = COUNT (DISTINCT CASE WHEN color IN ('BLUE', 'GREEN') THEN color END)
                    AND       0 = COUNT (         CASE WHEN color IN ('BLACK')         THEN color END)
                    ;
                    • 7. Re: How can I get an ID with particular condition
                      993017
                      Thank you very much Solomon and Frank. This is what I need.However Is there more efficient way than this because my table is around 8 mil rows.
                      • 8. Re: How can I get an ID with particular condition
                        Solomon Yakobson
                        990014 wrote:
                        Is there more efficient way than this because my table is around 8 mil rows.
                        You can try creating an index on column color, group_id combination and using:
                        select  group_id
                          from  t
                          where color in (
                                          'BLUE',
                                          'GREEN',
                                          'BLACK'
                                         )
                          group by group_id
                          having sum(distinct case color
                                                when 'BLUE' then 1
                                                when 'GREEN' then 2
                                                else 4
                                              end
                                    ) = 3
                        / 
                        In any case, post execution plan.

                        SY.