Select data from three tables - how to join SQL Srvr 05 Mgr Studio 9.

[CrystalLion]

I am new to SQL Server Management Studio. Using SQL Server 5, version 9.00.1399
--------------------------------
select TOP 100 PERCENT
dbo.A_DIM_TIME.DIM_YR_MO,dbo.A_DIM_TIME.DIM_YR,dbo.A_DIM_TIME.DIM_MO,
dbo.A_ALM_HISTORY.DIM_SOURCE_SYSTEM_ID,
dbo.A_DIM_SOURCE_SYSTEM.DIM_SOURCE_SYSTEM_NM,
dbo.A_ALM_HISTORY.AMORTIZATION_CD
from
dbo.A_ALM_HISTORY
where
dbo.A_ALM_HISTORY.DIM_TIME_ID
in ( select dbo.A_DIM_TIME.DIM_TIME_ID from dbo.A_DIM_TIME)
and dbo.A_ALM_HISTORY.DIM_SOURCE_SYSTEM_ID
in ( select dbo.A_DIM_SOURCE_SYSTEM.DIM_SOURCE_SYSTEM_ID from dbo.A_DIM_SOURCE_SYSTEM)
group by
A_DIM_TIME.DIM_YR_MO,A_DIM_TIME.DIM_YR,A_DIM_TIME.DIM_MO,
A_ALM_HISTORY.DIM_SOURCE_SYSTEM_ID,A_ALM_HISTORY.AMORTIZATION_CD,
A_DIM_SOURCE_SYSTEM.DIM_SOURCE_SYSTEM_NM,
A_ALM_HISTORY.AMORTIZATION_CD

RETURNS
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "A_DIM_TIME.DIM_MO" could not be bound.
etc.....

I am trying to retrieve the Source System Name and the Year Month

Can anyone hlep with this?

 

[MarkSweetland]

The column A_DIM_TIME.DIM_NO you are trying to output is contained inside the WHERE IN clause which is not 'visible' to select from. If you need to output the content of the table dbo.A_DIM_TIME, you'll have to do it with subquerries or JOINs. For example

select TOP 100 PERCENT 
		dbo.A_DIM_TIME.DIM_YR_MO,
		dbo.A_DIM_TIME.DIM_YR,
		dbo.A_DIM_TIME.DIM_MO,
		dbo.A_ALM_HISTORY.DIM_SOURCE_SYSTEM_ID,
		dbo.A_DIM_SOURCE_SYSTEM.DIM_SOURCE_SYSTEM_NM,
		dbo.A_ALM_HISTORY.AMORTIZATION_CD
from	
		dbo.A_ALM_HISTORY  
		JOIN dbo.A_DIM_TIME on dbo.A_DIM_TIME.DIM_TIME_ID  = dbo.A_ALM_HISTORY.DIM_TIME_ID
		JOIN dbo.A_DIM_SOURCE_SYSTEM on dbo.A_ALM_HISTORY.DIM_SOURCE_SYSTEM_ID = dbo.A_DIM_SOURCE_SYSTEM.DIM_SOURCE_SYSTEM_ID

group by 
		A_DIM_TIME.DIM_YR_MO, A_DIM_TIME.DIM_YR, A_DIM_TIME.DIM_MO, 
		A_ALM_HISTORY.DIM_SOURCE_SYSTEM_ID,
		A_ALM_HISTORY.AMORTIZATION_CD,
		A_DIM_SOURCE_SYSTEM.DIM_SOURCE_SYSTEM_NM,
		A_ALM_HISTORY.AMORTIZATION_CD

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach