select help - SQL Server Forums

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

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

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources