a little complex qry!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: a little complex qry!

  1. #1
    Join Date
    Aug 2000
    Posts
    194
    Hi:

    I have the following two tables:

    dept:
    -----

    id deptid amt
    ---- ------- -----
    1 2 100
    1 1 200
    1 null 500 --rec3
    1 3 800
    1 6 300
    2 4 450
    2 3 350


    dept2:
    -------

    id deptid amt parentdep
    ---- ------- ----- -----------
    1 2 100 null --prec1
    1 1 200 null --prec2
    1 4 500 2 --prec3
    1 3 800 1 --prec4
    1 6 300 null --prec5
    2 4 450 3 --prec6
    2 3 350 null --prec7



    Now, If the "deptid" is null for id =1, I want to retrieve the "amt" from dept table, else I want to get the sum of "amt"
    from "dept2" table for id = 1 and parentid is null.
    (note: There will be only one "null" deptid for a particular id)

    ie, I want a qry which returns,

    id amt
    ---- -----
    1 500 --rec3

    when there is a deptid with null in the dept table for id = 1.

    or:

    id amt
    ---- -----
    1 600 --prec1 + prec2 + prec5

    Pseudocode:
    --------------

    if there is a null deptid, for the given "id", I want to diplay the "id", "amt" from "dept"
    else
    display the "id", sum(dept) from dept2 where parentdep is null for the given "id"

    Can some one give me a qry to accomplish this?

    Thank you,

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    One of the possible solutions would be:

    (
    SELECT id, amt FROM dept WHERE id=1 AND deptid IS NULL
    )
    UNION ALL
    (
    SELECT id, SUM() amt FROM dept2 GROUP BY id
    WHERE id=1 AND parentdep IS NULL
    AND NOT EXISTS (SELECT id, amt FROM dept WHERE id=1 AND deptid IS NULL)
    );

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Aug 2000
    Posts
    194
    Thank you very very much jmodic!

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