13 Replies Latest reply: Jul 26, 2013 12:36 PM by nagarw31 RSS

    SQL self join

    KODS

      Dear All,

       

       

      Please help me.

       

      Here is my scenario

       

      table : test

       

      Service        Status

      -----------    -------------

      111        Active

      111        Active

      111        In Active

      222        In Active

      222        In Active

      333        In Active

      333        Active

       

       

      Here I need to display only 222 record. Table contains 145698325 record.

      Need to display the record where all the status is "In Active"

       

      I wrote a query but it is not giving the proper results. For reference I am published the query here.

      select service_id from asset

      group by service_id,status having count(service_id)=count(Status) and status='In Active';

       

       

       

      Thanks

      Kods

        • 1. Re: SQL self join
          chris227

          One way, corrected due the hint of Etbin, thanks.

           

          select

          service_id

          from asset

          group by service_id

          having

          count(case when status = 'In Active' then 1 else null end)

          =

          count(*)

           

          To include the status in the projection add

          ,max(status) keep (dense_rank first order by service_id) status

          or

          ,'In Active' status

          to the projection

           

          Message was edited by: chris227: correction

          • 2. Re: SQL self join
            Etbin

            Maybe (if you don't want ORA-00979: not a GROUP BY expression)

             

            select service_id

              from assets

            group by service_id

            having count(distinct status) = 1

               and max(status) = 'In Active'

             

            Regards

             

            Etbin

            • 3. Re: SQL self join
              Arild

              ..or

              count(*) = sum(decode(status, 'Inactive', 1, 0))


              if you spell 'Inactive' correctly, that is.

              • 4. Re: SQL self join
                KODS

                Dear  chris227,

                 

                It is not giving wrong data like record with staus active and inactive are also coming. My requirement is to get the service id whose status is inactive.

                example

                query should display only 222 because, even it is repeated twice its status is "In Active".

                Query should not display records like 111 as it is associated with multiple status like "Active" and "In Active"

                 

                Thanks,

                Kods

                • 5. Re: SQL self join
                  chris227

                  I corrected it. The solution given by arild is also valid.

                  • 6. Re: SQL self join
                    chris227

                    You are right, thanks. Just coded it from the scratch.

                    • 7. Re: SQL self join
                      KODS

                      Dear Chris,

                       

                      Kindly give the full query. I am not good in SQL. I am unable to join the below part with main query mentioned by you. Sorry for the trouble caused by me.

                       

                      To include the status in the projection add

                      ,max(status) keep (dense_rank first order by service_id) status

                      or

                      ,'In Active' status

                      to the projection

                      • 8. Re: SQL self join
                        nagarw31

                        use analytical function..your problem will solve

                        • 9. Re: SQL self join
                          Arild

                          He's saying that the code depends on your requested ouput. If you want just the service ('222') or both columns.

                           

                          with testit as (

                          select '111' service, 'Active' status from dual union all

                          select '111' service, 'Active' status from dual union all

                          select '111' service, 'Inactive' status from dual union all

                          select '222' service, 'Inactive' status from dual union all

                          select '222' service, 'Inactive' status from dual union all

                          select '333' service, 'Inactive' status from dual union all

                          select '333' service, 'Active' status from dual

                          )

                          select service --, max(status) keep (dense_rank first order by service) status

                          from testit

                          group by service

                          having count(*) = sum(decode(status, 'Inactive', 1, 0));

                           

                           

                          SERVICE

                          -------

                          222   

                          1 row selected.

                           

                           

                          If you want

                           

                          SERVICE STATUS 

                          ------- --------

                          222     Inactive

                          1 row selected.

                           

                          instead, then you'll need to uncomment that part above.

                          • 10. Re: SQL self join
                            chris227

                            select

                            service_id

                            ,max(status) keep (dense_rank first order by service_id) status

                            from asset

                            group by service_id

                            having

                            count(case when status = 'In Active' then 1 else null end)

                            =

                            count(*)

                             

                            or simpler the below, as you know the status already

                             

                            select

                            service_id

                            ,'In Active' status

                            from asset

                            group by service_id

                            having

                            count(case when status = 'In Active' then 1 else null end)

                            =

                            count(*)

                            • 11. Re: SQL self join
                              Rahul_India

                              Or.But why count(*);

                               

                              with testit as (
                              select '111' service, 'Active' status from dual union all
                              select '111' service, 'Active' status from dual union all
                              select '111' service, 'Inactive' status from dual union all
                              select '222' service, 'Inactive' status from dual union all
                              select '222' service, 'Inactive' status from dual union all
                              select '333' service, 'Inactive' status from dual union all
                              select '333' service, 'Active' status from dual  union all
                              select '444' service, 'Inactive' status from dual
                              )
                              
                              
                              select service ,'Inactive'
                              from testit
                              group by service
                              HAVING
                              count(case when status = 'Inactive' then 1 else null end)=count(*);
                              
                              
                              • 12. Re: SQL self join
                                KODS

                                Dear ff3e2993-6cc4-4764-b684-12275cdf1ff7,

                                 

                                Kindly provide the SQL with the specified function as I am not good at SQL in depth.

                                • 13. Re: SQL self join
                                  nagarw31

                                  Please try the below written code..might work..-

                                   

                                  select service from (select service,count(service) service_new  from test group by service) t,(select service,count(status)  status_new where status ='IN ACTIVE' group by service) f

                                  where t.service_new = f.status_new