Answered by:
DataWarehouse Star Join with role play.

Question
-
can someone tell me how to correctly write the tsql to do the join.
I have a fact table with one roll.
declare @DevelopmentFact TABLE
(
designCountrySK int
, deployCountrySK int
)
insert into @DevelopmentFact
select 1, 2
go
The dimension Table @CountryDim list 2 different countries.
declare @CountryDim TABLE
(
SK int identity(1,1)
, CountryName Varchar(10)
)
insert into @CountryDim
select 'India'
union
select 'USA'
The next query will show the CountryName for the deployCountry.
select *
from @DevelopmentFact f
join @CountryDim d
on f.deployCountrySK = d.SK
How to have both the deployCountry and designCountry returned?-- IBM has jobs for USA employee to work oversea (India) and get their wages.
Thursday, August 23, 2012 2:49 AM
Answers
-
thanks. how can i have USA and India both on the same row and each for the correct column, USA for deployCountry and India for designCountry?
-- IBM has jobs for USA employee to work oversea (India) and get their wages.
Are you looking something like below: Here I have used two instances of CountryDim one for joining to DesignCountry and other for joining to DeployCountry.
SELECT F.deployCountrySK, CDeploy.CountryName AS DeployCountry, F.designCountrySK, CDesign.CountryName AS DesignCountry FROM @DevelopmentFact F JOIN @CountryDim CDeploy ON F.deployCountrySK = CDeploy.SK JOIN @CountryDim CDesign ON F.designCountrySK = CDesign.SK
Thanks!- Marked as answer by light_wt Thursday, August 23, 2012 1:27 PM
Thursday, August 23, 2012 5:41 AM
All replies
-
Try
insert into @CountryDim (CountryName)
select 'India'
union
select 'USA'
select *
from @DevelopmentFact f
join @CountryDim d
on f.deployCountrySK = d.SK
or f.designCountrySK = d.SKMany Thanks & Best Regards, Hua Min
Thursday, August 23, 2012 2:58 AM -
thanks. how can i have USA and India both on the same row and each for the correct column, USA for deployCountry and India for designCountry?
-- IBM has jobs for USA employee to work oversea (India) and get their wages.
Thursday, August 23, 2012 3:04 AM -
If you select from @CountryDim, you can see India is with 1, USA with 2, right?
Many Thanks & Best Regards, Hua Min
Thursday, August 23, 2012 3:12 AM -
thanks. how can i have USA and India both on the same row and each for the correct column, USA for deployCountry and India for designCountry?
-- IBM has jobs for USA employee to work oversea (India) and get their wages.
Are you looking something like below: Here I have used two instances of CountryDim one for joining to DesignCountry and other for joining to DeployCountry.
SELECT F.deployCountrySK, CDeploy.CountryName AS DeployCountry, F.designCountrySK, CDesign.CountryName AS DesignCountry FROM @DevelopmentFact F JOIN @CountryDim CDeploy ON F.deployCountrySK = CDeploy.SK JOIN @CountryDim CDesign ON F.designCountrySK = CDesign.SK
Thanks!- Marked as answer by light_wt Thursday, August 23, 2012 1:27 PM
Thursday, August 23, 2012 5:41 AM -
Yes, Deepak. thanks.
-- IBM has jobs for USA employee to work oversea (India) and get their wages.
Thursday, August 23, 2012 1:28 PM