This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Dec 3, 2012 4:35 AM by 711584 RSS

Problem  While Writing SubQuery

711584 Newbie
Currently Being Moderated
Hi:

There are three tables,

1.TbReq
req_no,req_name
Data:
1 Req1
2 Req2
3 Req3

2.TbSts
sts_no,st_name
Data:
1 Approved
2 Pending
3 Downloaded


3.TbReqTrn
trn_no,req_no,sts_no
Data:

1 1 1
2 1 2
3 2 2

Required O/p:*

Reuest Type | NoOfRequest| NoOfStatusPending

Req1| 2 | 1
Req2| 1 | 1
Req3| 0 | 0




From Above tables i want to write query for,

select total number of requests available in TbReqTrn against each request from TbReq
and total number of status where st_name like '*Pending*'(and we have to take this count also from TbReqTrn table)

So, how to write this query...?

Can any body help me...


Thank You,
Anup Desai

Edited by: user11688398 on Dec 3, 2012 2:33 AM

Edited by: user11688398 on Dec 3, 2012 2:39 AM

Edited by: user11688398 on Dec 3, 2012 2:40 AM
  • 1. Re: Problem  While Writing SubQuery
    jeneesh Guru
    Currently Being Moderated
    Read FAQ: {message:id=9360002}

    Provide your db version, sample data (CREATE TABLE and INSERT statements) and expected output..
  • 2. Re: Problem  While Writing SubQuery
    ranit B Expert
    Currently Being Moderated
    1.TbReq
    req_no,req_name

    2.TbSts
    sts_no,st_name

    3.TbReqTrn
    trn_no,req_no,sts_no

    From Above tables i want to write query for,

    select total number of requests available in TbReqTrn against each request from TbReq
    and total number of status where st_name like '*Pending*'(and we have to take this count also from TbReqTrn table)
    Try this... NOT TESTED...
    /* Formatted on 12-3-2012 3:57:31 PM (QP5 v5.163.1008.3004) */
    SELECT COUNT (a.trn_no)
      FROM tbreqtrn a, tbreq b, tbsts c
     WHERE a.regno = b.regno AND a.sts_no = c.sts_no AND c.st_name = 'Pending';
  • 3. Re: Problem  While Writing SubQuery
    Most Wanted!!!! Journeyer
    Currently Being Moderated
    try this
    SELECT COUNT (a.req_no)
      FROM tbreq a
     WHERE EXISTS (
              SELECT 1
                FROM tbreqtrn b
               WHERE a.req_no = b.req_no
                 AND EXISTS (
                          SELECT 1
                            FROM tbsts c
                           WHERE b.sts_no = c.sts_no
                                 AND c.st_name LIKE '%PENDING%'));
    SELECT COUNT (a.req_no)
      FROM tbreq a
     WHERE EXISTS (
              SELECT 1
                FROM tbreqtrn b
               WHERE a.req_no = b.req_no
                 AND EXISTS (
                          SELECT 1
                            FROM tbsts c
                           WHERE b.sts_no = c.sts_no
                                 AND c.st_name ='PENDING'));
    regards,
    friend

    Edited by: most wanted!!!! on Dec 3, 2012 2:44 AM

    Edited by: most wanted!!!! on Dec 3, 2012 2:46 AM
  • 4. Re: Problem  While Writing SubQuery
    711584 Newbie
    Currently Being Moderated
    Hi:

    Please check some sample I/O:


    There are three tables,

    1.TbReq
    req_no,req_name
    Data:
    1 Req1
    2 Req2
    3 Req3

    2.TbSts
    sts_no,st_name
    Data:
    1 Approved
    2 Pending
    3 Downloaded


    3.TbReqTrn
    trn_no,req_no,sts_no
    Data:

    1 1 1
    2 1 2
    3 2 2

    Required O/p:

    Reuest Type | NoOfRequest| NoOfStatusPending

    Req1| 2 | 1
    Req2| 1 | 1
    Req3| 0 | 0




    Thank You,
    Anup Desai
  • 5. Re: Problem  While Writing SubQuery
    711584 Newbie
    Currently Being Moderated
    Hi:

    Please check my Input and required output


    Thank You,
    Anup Desai
  • 6. Re: Problem  While Writing SubQuery
    Most Wanted!!!! Journeyer
    Currently Being Moderated
    WITH tbreq AS
         (SELECT 1 AS req_no, 'req1' AS req_name
            FROM DUAL
          UNION ALL
          SELECT 2 AS req_no, 'req2' AS req_name
            FROM DUAL
          UNION ALL
          SELECT 3 AS req_no, 'req3' AS req_name
            FROM DUAL),
         tbsts AS
         (SELECT 1 AS sts_no, 'APPROVED' AS st_name
            FROM DUAL
          UNION ALL
          SELECT 2 AS sts_no, 'PENDING' AS st_name
            FROM DUAL
          UNION ALL
          SELECT 3 AS sts_no, 'DOWNLOADED' AS st_name
            FROM DUAL),
         tbreqtrn AS
         (SELECT 1 AS trn_no, 1 AS req_no, 1 AS sts_no
            FROM DUAL
          UNION ALL
          SELECT 2 AS trn_no, 1 AS req_no, 2 AS sts_no
            FROM DUAL
          UNION ALL
          SELECT 3 AS trn_no, 2 AS req_no, 2 AS sts_no
            FROM DUAL)
    SELECT   a.req_name AS req_type, COUNT (a.req_no), COUNT (c.sts_no)
        FROM tbreq a, tbreqtrn b, tbsts c
       WHERE a.req_no = b.req_no
         AND b.sts_no = c.sts_no
         AND c.st_name LIKE '%PENDING%'
    GROUP BY a.req_name, a.req_no, c.sts_no;
  • 7. Re: Problem  While Writing SubQuery
    711584 Newbie
    Currently Being Moderated
    No, It gives me wrong output.

    Thank You,
    Anup Desai
  • 8. Re: Problem  While Writing SubQuery
    jeneesh Guru
    Currently Being Moderated
    select req.req_name,
           sum(decode(trn.req_no,null,0,1)) req_count,
           sum(decode(sts.st_name,'Pending',1,0)) pend_count
    from tbreqtrn trn right outer join tbreq req
      on (trn.req_no = req.req_no)
      left outer join tbsts sts
      on (sts.sts_no = trn.sts_no)
    group by req.req_name  
    order by 1;
    Or the "+" notation
    select req.req_name,
           sum(decode(trn.req_no,null,0,1)) req_count,
           sum(decode(sts.st_name,'Pending',1,0)) pend_count
    from tbreqtrn trn,tbreq req,tbsts sts
    where trn.req_no(+) = req.req_no
    and sts.sts_no(+) = trn.sts_no
    group by req.req_name  
    order by 1;
    Edited by: jeneesh on Dec 3, 2012 4:54 PM
  • 9. Re: Problem  While Writing SubQuery
    711584 Newbie
    Currently Being Moderated
    In this query sum(decode(sts.st_name,'Pending',1,0)) pend_count gets fail. Because in my case i want check names like, Approval Pending , Pending ,Download Pending...etc. So i want to use like'%Pending%'

    So how to solve it?


    Thank You,
    Anup Desai
  • 10. Re: Problem  While Writing SubQuery
    jeneesh Guru
    Currently Being Moderated
    Use CASE
    sum(case when lower(sts.st_name) like '%pending%' then 1 else 0 end) pend_count
  • 11. Re: Problem  While Writing SubQuery
    Most Wanted!!!! Journeyer
    Currently Being Moderated
    WITH tbreq AS
         (SELECT 1 AS req_no, 'REQ1' AS req_name
            FROM DUAL
          UNION ALL
          SELECT 2 AS req_no, 'REQ2' AS req_name
            FROM DUAL
          UNION ALL
          SELECT 3 AS req_no, 'REQ3' AS req_name
            FROM DUAL),
         tbsts AS
         (SELECT 1 AS sts_no, 'APPROVED' AS st_name
            FROM DUAL
          UNION ALL
          SELECT 2 AS sts_no, 'PENDING' AS st_name
            FROM DUAL
          UNION ALL
          SELECT 3 AS sts_no, 'DOWNLOADED' AS st_name
            FROM DUAL),
         tbreqtrn AS
         (SELECT 1 AS trn_no, 1 AS req_no, 1 AS sts_no
            FROM DUAL
          UNION ALL
          SELECT 2 AS trn_no, 1 AS req_no, 2 AS sts_no
            FROM DUAL
          UNION ALL
          SELECT 3 AS trn_no, 2 AS req_no, 2 AS sts_no
            FROM DUAL)
    SELECT DISTINCT req_name,
                    SUM (CASE
                            WHEN a.req_no <> c.req_no
                               THEN 0
                            ELSE 1
                         END) AS no_of_req,
                    CASE
                       WHEN b.st_name LIKE '%PEN%'
                          THEN 1
                       ELSE 0
                    END no_of_pend_status
               FROM tbreq a, tbsts b, tbreqtrn c
              WHERE st_name LIKE '%PEN%'
           GROUP BY a.req_name, b.st_name
           ORDER BY req_name
  • 12. Re: Problem  While Writing SubQuery
    jeneesh Guru
    Currently Being Moderated
    most wanted!!!! wrote:
    WITH tbreq AS
    (SELECT 1 AS req_no, 'REQ1' AS req_name
    FROM DUAL
    UNION ALL
    SELECT 2 AS req_no, 'REQ2' AS req_name
    FROM DUAL
    UNION ALL
    SELECT 3 AS req_no, 'REQ3' AS req_name
    FROM DUAL),
    tbsts AS
    (SELECT 1 AS sts_no, 'APPROVED' AS st_name
    FROM DUAL
    UNION ALL
    SELECT 2 AS sts_no, 'PENDING' AS st_name
    FROM DUAL
    UNION ALL
    SELECT 3 AS sts_no, 'DOWNLOADED' AS st_name
    FROM DUAL),
    tbreqtrn AS
    (SELECT 1 AS trn_no, 1 AS req_no, 1 AS sts_no
    FROM DUAL
    UNION ALL
    SELECT 2 AS trn_no, 1 AS req_no, 2 AS sts_no
    FROM DUAL
    UNION ALL
    SELECT 3 AS trn_no, 2 AS req_no, 2 AS sts_no
    FROM DUAL)
    SELECT DISTINCT req_name,
    SUM (CASE
    WHEN a.req_no <> c.req_no
    THEN 0
    ELSE 1
    END) AS no_of_req,
    CASE
    WHEN b.st_name LIKE '%PEN%'
    THEN 1
    ELSE 0
    END no_of_pend_status
    FROM tbreq a, tbsts b, tbreqtrn c
    WHERE st_name LIKE '%PEN%'
    GROUP BY a.req_name, b.st_name
    ORDER BY req_name
    Lot of issues in the code..

    1. No need to use DISTINCT, when you are GROUPING
    2. No join conditions applied...outer join is required..
    3. Unwanted filter.
    4. Unwantd column in the GROUP BY list
  • 13. Re: Problem  While Writing SubQuery
    Most Wanted!!!! Journeyer
    Currently Being Moderated
    thank you for identifying the issues jeneesh am a newbie i have just started learning.

    Edited by: most wanted!!!! on Dec 3, 2012 3:59 AM
  • 14. Re: Problem  While Writing SubQuery
    711584 Newbie
    Currently Being Moderated
    Hi this query works fine. But last thing is that i want to display only such records whose req_count>0


    Thank You,
    Anup Desai
1 2 Previous Next

Legend

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