-
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,
-
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,
-
Thank you very very much jmodic!
