Self Joins - State Table

• Joe Contreras-290946

  SSCommitted

  Points: 1903

  More actions

  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,

• Joe Contreras-290946

  SSCommitted

  Points: 1903

  More actions

  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,