SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gangadhara.ms
Aged Yak Warrior

India
547 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
Constraint Violating Yak Guru

Romania
419 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
547 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
Constraint Violating Yak Guru

Romania
419 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
52317 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
Constraint Violating Yak Guru

Romania
419 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  
 New 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.05 seconds. Powered By: Snitz Forums 2000