Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 select help

Author  Topic 

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2013-07-10 : 01:44:28
Dear All,

I have a 2 table
Ex: 1st Table Name: emp_tran
ID name category title
1 A Helper Dr.
2 B Nurse Ms.

2nd table: map_list
Listtype listID Listname
category 1 Physician
category 2 Nurse
category 3 Helper
title 1 Dr.
title 2 Ms.

I need to pick the ID from map_list corresponding to each type

Output
ID Name List_ID
1 A 3
2 B 2


Please help.



Thanks,
Gangadhara MS
SQL Developer and DBA

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-07-10 : 02:07:42
Hi,

the join field is Category (from emp_tran) and ListName(from map_list)


SELECT
E.ID
,E.Name
,M.ListID

FROM
emp_tran as E
INNER JOIN map_list as M
ON E.category=M.ListName




Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2013-07-10 : 02:14:47
If I need to get again title ID(ListID) from the "map_list" will be a problem ..

Thanks,
Gangadhara MS
SQL Developer and DBA
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-07-10 : 03:06:15
I don't exactly understand about title ID

maybe this would help (filtred on categ)



SELECT
E.ID
,E.Name
,M.ListID

FROM
emp_tran as E
INNER JOIN
(SELECT
M.ListID
, M.ListName
FROM map_list as M
WHERE M.Listtype='category') M
ON E.category=M.ListName




S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-10 : 03:10:45
[code]
SELECT *
FROM
(
SELECT m.ID, m.name,m.Listtype,n.List_ID
FROM
(
SELECT *
FROM emp_tran
UNPIVOT (Val FOR Listtype IN ([category],[title]))u
)m
INNER JOIN map_list n
ON n.Listtype = m.Listtype
AND n.Listname = m.Val
)t
PIVOT(MAX(List_ID) FOR Listtype IN ([category],[title]))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-07-10 : 03:11:17
I re-read and come with:

SELECT
E.ID
,E.Name
,M.ListID
,T.ListID as TitleID

FROM
emp_tran as E
INNER JOIN

(SELECT
M.ListID
, M.ListName
FROM map_list as M
WHERE M.Listtype='category') M
ON E.category=M.ListName

INNER JOIN

(SELECT
M.ListID
, M.ListName
FROM map_list as M
WHERE M.Listtype='Title') T
ON E.Title=T.ListName

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page
   

- Advertisement -