a little complex qry!

[gsprince]

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,

[jmodic]

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,

[gsprince]

Thank you very very much jmodic!