|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/18/2009 8:46:42 PM
Posts: 2,
Visits: 19
|
|
I have 2 table with below data
Manager_ID | Manager_Name | COMPANY
CARE | ANDY | COMP A
CARE | JOHN | COMP B
CARE | CHRIS | COMP C
A001 | ANDY | COMP D
A002 | MABEL | COMP E
A003 | LANDY |COMP F
MANAGER_ID | MANAGER_NAME
A001 | ANDY
A002 | MABEL
A003 | LANDY
A004 | JOHN
A005 | CHRIS
I want the table A to have one more column (Manager ID_2). If the manager id = care, then this new column will get the Manager ID from Table 2, using Table1.Manager_Name = Table2.Manager_Name. When the manager id <> care, then the new column will remain as Table1.Manager_ID.
Manager_ID | Manager_Name | COMPANY | Manager_ID2
CARE | ANDY | COMP A | A001
CARE | JOHN | COMP B | A004
CARE | CHRIS | COMP C | A005
A001 | ANDY | COMP D | A001
A002 | MABEL | COMP E | A002
A003 | LANDY |COMP F | A003
My statement is
Select Manager_ID, Manager_Name, COMPANY,
case when T1.Manager_ID like '%CARE%' then
(
(SELECT T2.Manager_ID from DB.Table2 T2 where T1.Manager_Name=T2.Manager_Name)
else
T1.Manager_ID
END as Manager_ID2
)
From DB.Table1 T1
But this code seems got problem..anyone can help?
Thanks!
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 2 days ago @ 7:45:03 AM
Posts: 39,
Visits: 169
|
|
You can try this
SELECT
T1.Manager_ID,
T1.Manager_Name,
T1.COMPANY,
T2.Manager_ID AS Manager_ID2
FROM DB.Table1 T1,
DB.Table2 T2
WHERE T1.Manager_Name = T2.Manager_Name
AND T1.Manager_ID like '%CARE%'
UNION ALL
SELECT
T1.Manager_ID,
T1.Manager_Name,
T1.COMPANY,
T1.Manager_ID AS Manager_ID2
FROM DB.Table1 T1
WHERE T1.Manager_ID NOT like '%CARE%'
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/5/2009 4:04:32 AM
Posts: 1,
Visits: 2
|
|
SELECT
T1.Manager_ID,
T1.Manager_Name,
T1.COMPANY,
Case when T1.Manager_id = 'CARE' and T1.Manger_Name = T2.Manager_Name
then T2.Manager_ID
ELSE T1.Manger_Id
END as Manager_ID2
From
Table1 T1
left join
Table2 T2
on T1.Manager_Name = T2.Manager_Name
|
|
|
|