locked
DataWarehouse Star Join with role play. RRS feed

  • 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.SK

    Many 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