how to join two tables with non equal conditions?

• sliu

  Old Hand

  Points: 329

  More actions

  November 30, 2011 at 10:51 pm

  #248753

  I have two tables:

  create table #A(aYr int, aCol int)

  insert #A(aYr,aCol) values (2008, 2)

  insert #A(aYr,aCol) values (2009, 5)

  insert #A(aYr,aCol) values (2010, 9)

  insert #A(aYr,aCol) values (2011, 8)

  create table #b(bYr int, bCol int)

  insert #A(bYr,bCol) values (2008, 3)

  insert #A(bYr,bCol) values (2009, 4)

  I want join two tables,

  if aYr exists in bYr, then join two tables on aYr=bYr

  if aYr>=max(bYr) then aYr=max(bYr)

  the results will be

  aYr bYr aCol bCol

  2008 2008 2 3

  2009 2009 5 4

  2010 2009 9 4

  2011 2009 8 4

  how can I do this query. Thanks a lot.

• SpringTownDBA

  SSCertifiable

  Points: 6917

  More actions

  November 30, 2011 at 11:25 pm

  #1415031

  sliu (11/30/2011)

  ---

  I have two tables:

  create table #A(aYr int, aCol int)

  insert #A(aYr,aCol) values (2008, 2)

  insert #A(aYr,aCol) values (2009, 5)

  insert #A(aYr,aCol) values (2010, 9)

  insert #A(aYr,aCol) values (2011, 8)

  create table #b(bYr int, bCol int)

  insert #A(bYr,bCol) values (2008, 3)

  insert #A(bYr,bCol) values (2009, 4)

  I want join two tables,

  if aYr exists in bYr, then join two tables on aYr=bYr

  if aYr>=max(bYr) then aYr=max(bYr)

  the results will be

  aYr bYr aCol bCol

  2008 2008 2 3

  2009 2009 5 4

  2010 2009 9 4

  2011 2009 8 4

  how can I do this query. Thanks a lot.

  here's 1 of many solutions.

  select aYr, bYr, aCol, bCol

  from #a inner join #b on aYr = bYr

  union all

  select aYr, bYr, aCol, bCol

  from #a cross join #b

  where aYr > (select max(bYr) from #b)

  and bYr = (select max(bYr) from #b)

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  December 1, 2011 at 6:07 am

  #1415207

  Here's the responses from the other version of this post -

  Cadavre (12/1/2011)

  ---

  SELECT aYr, bYr, aCol, CASE WHEN aYr = bYr

  THEN bCol

  ELSE MAX(bCol) OVER (PARTITION BY (SELECT NULL)) END AS bCol

  FROM #A a

  INNER JOIN #b b ON 1=1

  WHERE aYr = bYr OR aYr = bYr+1

  ChrisM@Work (12/1/2011)

  ---

  SELECT aYr, bYr, aCol, bCol

  FROM #A a

  CROSS APPLY (

  SELECT MAX_bYr = MAX(bYr) FROM #b) x

  INNER JOIN #b b

  ON b.bYr = CASE WHEN a.aYr <= MAX_bYr THEN a.aYr ELSE MAX_bYr END

  Cadavre (12/1/2011)

  ---

  SELECT aYr, bYr, aCol, bCol

  FROM #A a

  INNER JOIN (SELECT bYr, bCol, MAX(bYr) OVER (PARTITION BY (SELECT NULL)) AS maxbYr

  FROM #b) b ON b.bYr = CASE WHEN a.aYr <= maxbYr

  THEN a.aYr

  ELSE maxbYr END

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• sliu

  Old Hand

  Points: 329

  More actions

  December 1, 2011 at 10:01 am

  #1415392

  Thanks a lot. It worked perfect.:-P