DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: HELP NEEDED IMMEDIATELY

  1. #1
    Join Date
    Sep 2001
    Posts
    24

    Post

    Hi,
    There are two tables.
    SQL> desc test
    Name Null? Type
    X NUMBER
    Y NUMBER

    SQL> desc test1
    Name Null? Type

    P NUMBER

    SQL> select p a ,count(1) b FROM TEST1 GROUP BY P
    2 /

    A B
    ---------- ----------
    1 3
    2 2

    SQL> select p1,COUNT(1) c FROM TEST2 GROUP BY P1
    2 /

    P1 C
    ---------- ----------
    1 4


    I have written a query (given below)
    select a.a, a.b, b.c
    FROM (select p a ,count(1) b FROM TEST1 GROUP BY P) a,
    (select p1,COUNT(1) c FROM TEST2 GROUP BY P1) b

    A B C
    ---------- ---------- ----------
    1 3 4
    2 2 4

    but for the second row (2 2 4 ) the c value should be null.
    My requirement is for the matching records in both the queries the answer should come for C else it should return null.

    Thanks in Advance



  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    that is a simple join, just add your join condition (if any)

  3. #3
    Join Date
    Sep 2001
    Posts
    24


    hi
    SQL> select a.a, a.b, b.c
    2 FROM (select p a ,count(1) b FROM TEST1 GROUP BY P) a,
    3 (select p1 c1,COUNT(1) c FROM TEST2 GROUP BY P1) b
    4 where a.a =b.c1
    5 /

    A B C
    ---------- ---------- ----------
    1 3 4

    1 select a.a, a.b, b.c
    2 FROM (select p a ,count(1) b FROM TEST1 GROUP BY P) a,
    3 (select p1 c1,COUNT(1) c FROM TEST2 GROUP BY P1) b
    4* where a.a !=b.c1
    5 /

    A B C
    --------- ---------- ----------
    2 2 4


    if you see my join for != condition it returns 4 but the answer should be null. how to avoid the 4 there (and thru a single query)


    Thanks in advance.




    Shyla

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    outer join

  5. #5
    Join Date
    Sep 2001
    Posts
    24

    1 select a.a, a.b, b.c
    2 FROM (select p a ,count(1) b FROM TEST1 GROUP BY P) a,
    3 (select p1,COUNT(1) c FROM TEST2 GROUP BY P1) b
    4* where a.a = b.c(+)
    SQL> /

    A B C
    ---------- ---------- ----------
    1 3
    2 2


    When i use the outer join the query returns as above.

    Regards
    Shyla

    Shyla

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    i wonder why you compare a.a and b.c

  7. #7
    Join Date
    Sep 2001
    Posts
    24
    Thanks a lot .

    select a.a, a.b, b.c
    FROM (select p a ,count(1) b FROM TEST1 GROUP BY P) a,
    (select p1,COUNT(1) c FROM TEST2 GROUP BY P1) b
    where a.a = b.p1(+)
    /

    A B C
    ----- ---------- ----------
    1 3 4
    2 2


    The answer has come but if we use the select in a select will it decrease the performance ?

    Regards
    Shyla

    Shyla

Posting Permissions

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


Click Here to Expand Forum to Full Width