Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    1

    Unanswered: select records from one table & remaining from the other

    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 !

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: select records from one table & remaining from the other

    Originally posted by mbk
    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

Posting Permissions

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