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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 select help
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gangadhara.ms
Aged Yak Warrior

India
549 Posts

Posted - 07/10/2013 :  01:44:28  Show Profile  Reply with Quote
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

Romania
545 Posts

Posted - 07/10/2013 :  02:07:42  Show Profile  Reply with Quote
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

India
549 Posts

Posted - 07/10/2013 :  02:14:47  Show Profile  Reply with Quote
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

Romania
545 Posts

Posted - 07/10/2013 :  03:06:15  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 07/10/2013 :  03:10:45  Show Profile  Reply with Quote

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


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

stepson
Aged Yak Warrior

Romania
545 Posts

Posted - 07/10/2013 :  03:11:17  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000