This discussion is archived
13 Replies Latest reply: Jul 26, 2013 10:36 AM by nagarw31 RSS

SQL self join

KODS Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    ..or

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


    if you spell 'Inactive' correctly, that is.

  • 4. Re: SQL self join
    KODS Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

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

  • 6. Re: SQL self join
    chris227 Guru
    Currently Being Moderated

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

  • 7. Re: SQL self join
    KODS Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    use analytical function..your problem will solve

  • 9. Re: SQL self join
    Arild Explorer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points