Need help with join condition based on calculation

• rash3554

  SSCommitted

  Points: 1984

  More actions

  December 3, 2015 at 4:16 am

  #313581

  Hi there,

  I need some help with writing the Join condition based on calculation

  I have two tables Table1 and Table2

  Create table Table1(id int, CalculationRules Varchar(50))

  Insert into Table1 values(1,1000);

  Insert into Table1 values(2,1001);

  Insert into Table1 values(3,1002);

  Insert into Table1 values(4,1003+1004);

  Create table Table2(Variableid int, Value int)

  Insert into Table1 values(1000,10);

  Insert into Table1 values(1001,20);

  Insert into Table1 values(1002,30);

  Insert into Table1 values(1003,40);

  Insert into Table1 values(1004,50);

  I want to do this

  Select ID,Value

  from Table1 inner join

  Table2 on

  CalculationRules=VariableID;

  But Id=4 from table1 is a addition of 2 values and hence failing..how can I do this?

  Expected Output:

  ID Value

  1 10

  2 20

  3 30

  4 90

  Thanks

  Rash

• GroverVivek

  SSCommitted

  Points: 1674

  More actions

  December 3, 2015 at 6:47 am

  #1843750

  Please specify about output you are expecting.

  Thanks

  Regards
  VG

• rash3554

  SSCommitted

  Points: 1984

  More actions

  December 3, 2015 at 6:54 am

  #1843753

  I just added the expected output

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  December 3, 2015 at 6:56 am

  #1843754

  If you add 1003 and 1004, you'll get 2007. If you want to preserve the two values, you'll need to do something like this:

  Insert into Table1 values(1,1000);

  Insert into Table1 values(2,1001);

  Insert into Table1 values(3,1002);

  Insert into Table1 values(4,1004);

  Insert into Table1 values(4,1003);

  If you can tell us what you're trying to do here, and why, we may be able to suggest a better way.

  John

• GroverVivek

  SSCommitted

  Points: 1674

  More actions

  December 3, 2015 at 7:45 am

  #1843768

  Yes. I'm agreed with John.

  Thanks,

  Vivek

  Regards
  VG