Hello - I'm looking for a little advanced sql advice on a project i'm working on. Here's the lowdown:
I have two tables - we'll call them A and B. Table A has one row for every customer in the database and fifteen columns with a number in them specific to the customer. Table B has a hard coded set of decimal numbers that correspond to the columns of Table A. There is no common "join" between the two tables other than the column headers of Table A and a possible field in table B.
What I need to do is perform a rather lengthy math calculation using one field from table A multiplied to the variable in table B. It would be easy to do if I could join the tables together on something - but I'm not aware of a way to join tables from one field to another's column headers. I thought about pulling all the values into a temp table and doing the math from there, but the tables may change structure over time and according to other math formulas.
Is there a better way to get the math done than writing out, say, "select (tablea.field1 * tableb.field1)+(tablea.field2 * tableb.field2)+... on down the line?
Any advice or guidance is appreciated. Thanks!
RJ
Hi Richard,
Are you not able to join Table B to another table which can then be joined to table A? For example:
Select A.SomeField, B.SomeField from A
Join C on A.SomeField = C.SomeField
Join B on C.SomeField = B.someField
and use this to create a SQL view so you can use that for referencing and calculations - it's then like working with just one table.
Cheers,
Kevin
Hi Kevin,
You should be able to pick up the column name from sysobjects or syscolumns. They link on the id field.
Debbie