Post reply

Self Joins - State Table

  • December 5, 2008 at 8:42 am

    #194725

    Hi,

    I have a state table that contains the following fields:

    CountryCode,StateCode. I need to do the following tasks on this table.

    Create a new column call OriginState and a secondColumn call DestinationState. My results should look like the following:

    OriginState: DestinationState:

    US-WA US-MA

    US-WA US-CT

    US-WA US-FL

    In the above example the state needs to duplicate 50 times in the originstate and the destinationstate needs to be have the unique 50 state entries; my total records should be 50 * 50 = 2500.

    This is what I have so far

    Select s1.statecode,s1.countrycode + '-' + s1.statecode OriginCode,

    s2.countrycode + '-' + s2.statecode DestinationCode

    From state s1 Left Join state s2

    On s1.statecode = s2.statecode

    Thanks,

  • December 5, 2008 at 8:50 am

    #908383

    Figured it out. It's not a self-join but rather a cross-join that I need.

    Here's my Sql that yields my results:

    Select s1.statecode,s1.countrycode + '-' + s1.statecode OriginCode,

    s2.countrycode + '-' + s2.statecode DestinationCode

    From state s2 Cross Join state s1

    Thanks,

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply