Joining one field to many fields

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 2000 Forums

Transact-SQL (2000)

Joining one field to many fields

Author

Topic

cpat
Starting Member

5 Posts

Posted - 2004-01-26 : 11:20:02

I have 2 tables, tblComponentTest and tblEmployeeTest. I need to create a view that lists each employee id, their monthly component (JanComponent, FebComponent, etc.), and the target for that component (from tblComponentTest) for each month. I tried joining JanComponent to tblComponentTest.component and FebComponent totblComponentTest.component, etc. and then using case statements to get the target for each month, but it didn't work. I would bet that this is an easy fix for most of you.

The resulting view should look like this:
emp_id JanComponent JanTarget FebComponent FebTarget MarComponent MarTarget...
1000 AA 3 AA 3 BB 4.5
1001 DD 1.75 DD 1.75 DD 1.75
1002 EE 4.75 AA 3 AA 3
...

Here are the tables and data:
CREATE TABLE [tblComponentTest] (
[component] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[target] [float] NULL

CREATE TABLE [tblEmployeeTest] (
[emp_id] [float] NULL ,
[JanComponent] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FebComponent] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MarComponent] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AprComponent] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MayComponent] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JunComponent] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

INSERT tblComponentTest VALUES('AA',3)
INSERT tblComponentTest VALUES('BB',4.5)
INSERT tblComponentTest VALUES('CC',2)
INSERT tblComponentTest VALUES('DD',1.75)
INSERT tblComponentTest VALUES('EE',4.75)
INSERT tblComponentTest VALUES('FF',3.5)

INSERT tblEmployeeTest VALUES(1000,'AA', 'AA', 'BB', 'BB', 'BB','BB')
INSERT tblEmployeeTest VALUES(1001,'DD', 'DD', 'DD', 'DD', 'DD','DD')
INSERT tblEmployeeTest VALUES(1002,'EE', 'AA', 'AA', 'AA', 'BB','BB')
INSERT tblEmployeeTest VALUES(1003,'FF', 'FF', 'FF', 'FF', 'FF','FF')
INSERT tblEmployeeTest VALUES(1004,'AA', 'AA', 'CC', 'CC', 'CC','CC')

Thank you!

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-01-26 : 11:48:36

Try this

SELECT emp_id,
JanComponent, JanTarget = c1.target,
FebComponent, FebTarget = c2.target,
MarComponent, MarTarget = c3.target,
AprComponent, AprTarget = c4.target,
MayComponent, MayTarget = c5.target,
JunComponent, JunTarget = c6.target
FROM tblEmployeeTest e
LEFT JOIN tblComponentTest c1 ON e.JanComponent = c1.component
LEFT JOIN tblComponentTest c2 ON e.FebComponent = c2.component
LEFT JOIN tblComponentTest c3 ON e.MarComponent = c3.component
LEFT JOIN tblComponentTest c4 ON e.AprComponent = c4.component
LEFT JOIN tblComponentTest c5 ON e.MayComponent = c5.component
LEFT JOIN tblComponentTest c6 ON e.JunComponent = c6.component

Raymond

stephe40
Posting Yak Master

218 Posts

Posted - 2004-01-26 : 11:51:27

Or try this... its a little different. You might want to theck the execution plans and see which one is best. They will probably both result in nested loop joins, if there isnt much data in the table.


select e.emp_id, 
e.janComponent, (select target from tblComponentTest c where c.component = e.janComponent) as janComponentTarget,
e.febComponent, (select target from tblComponentTest c where c.component = e.febComponent) as febComponentTarget,
e.marComponent, (select target from tblComponentTest c where c.component = e.marComponent) as marComponentTarget
from tblEmployeeTest e

cpat
Starting Member

5 Posts

Posted - 2004-01-26 : 12:04:30

Thank you so much! Raymond - I tried yours and it works perfectly. Stephe40 - I'll give yours a try and see if it performs any better.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources