Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    3

    Unanswered: Problem in Correlated Sub Query

    Hi,

    I am new in oracle(SQL), could anyone help me in writing a correct SQL.

    Below is the table structure.

    Table: Subsc
    Fields:
    1. Sub_no (this field will hold values of subscriber nos, for e.g. S111111, S222222, S333333, S444444, etc.)
    2. s_status (this field will hold values for different status of subscriber, for e.g. 'A', 'S', 'C', etc.)
    3. cus_id (this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.)


    Table: Bill
    Fields:
    1. Bill_no this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.)
    2. b_status = (this field will hold values for different status of bill for e.g. 'O', 'C', 'S', etc.)


    Note:
    1. The Sub_no is a Primary key of Subsc table.
    2. The cus_id is a foreign in Subsc table (referred from Bill_no field of Bill table)
    3. The Bill_no field is the Primary key of Bill table.


    Query A --> I wrote a query to select cus_id/Bill_no which is in status open (b_status = 'O') and having more than two active subscriber (i.e. S_status = 'A') in it ( i.e. more the two subscribers in same bill).

    select s.cus_id
    from subsc s
    where exists (select 1 from bill
    where bill_no = s.cus_id
    and b_status = 'O')
    and s_status = 'A'
    group by s.cus_id
    having count(sub_no) = 2


    Problem : The above query will give the cus_id (or rather bill_no) which are in open status (b_status ='O) and which are having TWO ACTIVE Subscribers (s_status ='A') in it.
    However, this query will also lists the cus_id/bill_no which are having more than TWO subscribers in it
    (but only two subscriber will be in Active status (s_status = 'A') and the others will be in s_status = 'C' or s_status = 'S'.


    Inputs required: I want to write a query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it.



    Query B--> If I include the sub_no in the above query then NO row are returned.

    select s.cus_id, s.sub_no
    from subsc s
    where exists (select 1 from bill
    where bill_no = s.cus_id
    and b_status = 'O')
    and s_status = 'A'
    group by s.cus_id, s.sub_no
    having count(sub_no) = 2

    Inputs required: I want to modify the above query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it ALONG with the sub_no.


    Thanks a lot in advance.


    Regards,

    Nitin

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by nitin_chat_id
    I want to modify the above query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it ALONG with the sub_no.
    To answer that query you have the tables the wrong way around. You want to select open bills that have two active subscribers, but in fact you are selecting active subscribers that have open bills...

    Try:
    Code:
    select bill_no
    from   bill
    where  b_status = 'O'
    and    exists 
    ( select 1
      from   subsc s
      where  s.cus_id = bill.bill_no
      and    s_status = 'A'
      group by s.cus_id
      having count(sub_no) = 2 
    )
    or
    Code:
    select bill_no
    from   bill
    where  b_status = 'O'
    and    2 = 
    ( select count(*)
      from   subsc s
      where  s.cus_id = bill.bill_no
      and    s_status = 'A'
    )
    BTW, it is generally considered best practice to name related columns in a consistent manner - i.e. cus_id in both tables, or bill_no in both tables.

  3. #3
    Join Date
    Mar 2008
    Posts
    3
    Hi Tony,

    I have tried both the above mentioned queries and could still see these queries does not return the correct output.

    Both these queries will fetch the Bill_No which are having more than 2 active Subscribers (but only 2 Subscribers will be active and other Subscribers will have some other status like 'Cancelled' or 'Suspended).

    The output returned by both the queries are exactly the same.

    Also the "Query B" mentioned by me where I want to display the Bill_no along with the Sub_no is not included in your answer.

    Please help me in both the queries.

    Thanks a lot.

    Regards,
    Last edited by nitin_chat_id; 03-26-08 at 01:37.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    So you want just 2 active subscribers and no other subscribers at all?

    Try:
    Code:
    select bill_no
    from   bill
    where  b_status = 'O'
    and    2 = 
    ( select count(*)
      from   subsc s
      where  s.cus_id = bill.bill_no
      and    s_status = 'A'
    )
    and not exists
    ( select null
      from   subsc s
      where  s.cus_id = bill.bill_no
      and    s_status != 'A'
    )
    I don't really understand what your "Query B" is...

  5. #5
    Join Date
    Mar 2008
    Posts
    3

    Regd. Correlated Subquery

    Hi Tony,

    Thanks a lot.
    The latest query works, it gave me the correct output which I was looking for.

    Regarding "Query B", I meant that if I add Sub_no in the outer Select statement along with the cus_id then NO rows were displayed in my original query.

    I have done a small modification in your query and I am able to get the desired output. I have used your query as the subquery to get the output.

    Below is my modification in RED color:

    select s2.cus_id, s2.sub_no
    from subsc s2
    where s2.cus_id in (
    select bill.bill_no
    from bill
    where b_status = 'O'
    and 2 = ( select count(*)
    from subsc s
    where s.cus_id = bill.bill_no
    and s_status = 'A'
    )
    and not exists ( select null
    from subsc s
    where s.cus_id = bill.bill_no
    and s_status != 'A'
    )
    )


    Thanks a ton once again.

    Regards,
    Nitin
    Last edited by nitin_chat_id; 03-27-08 at 03:43.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •