Hi,

These are my tables :

Table-1
---------

user_id Dept Function Authority
------- ---- -------- ---------
91623 MKT Func_1 Y
91623 MKT Func_2 N
91623 MKT Func_3 Y
11122 ACC FUNC_1 N
.
.


Table-2
---------

Dept Function Authority
---- -------- ---------
MKT FUNC_1 Y
MKT FUNC_2 Y
MKT FUNC_3 Y
MKT FUNC_4 Y
MKT FUNC_5 N
.
.

MKT FUNC_17 Y
MKT FUNC_18 Y
.
.
.
ACC FUNC_1 Y
ACC FUNC_2 N




Table-2 holds all the functions and their default authority code. Table_1 holds the actual functions & authority given to an employee.

I am trying to write an SQL which will select for a user from Table_1 the user_id, Function and authority for whatever functions are in the table and for the missing functions, take them from Table_2. i.e. the result should look like :


user_id Function Authority
------- -------- ---------
-- Taken from Table_1
91623 Func_1 Y
91623 Func_2 N
91623 Func_3 Y
-- Taken from Table_2 (user_id from Table_1)
91623 FUNC_4 Y
91623 FUNC_5 N
.
.

91623 FUNC_17 Y
91623 FUNC_18 Y


Table_1 & Table_2 are linked by the department.

Could anyone help me with this query ?

I tried the following, but it does not work right :

select
a.user_id,
b.function,
min(isnull(a.authority,b.authority))
from
Table_1 a,
Table_2 b
where
a.user_id = 91623 and
a.dept = b.dept
group by
a.user_id,
b.function



Thanks !

You need to use UNION:

SELECT ... FROM table1
UNION
SELECT ... FROM table2