How to create a calculated column using data from other tables in SQL Server 2014?

Question

I have three tables(W,P,Q) each connected to the Primary key of another table(S). In order to calculate a field, I need to get data from the three tables(W,P,Q). I know that in SQL Server you can only create a calculated column with data from the SAME table.

I was wondering if there's any other way to solve my problem without merging the three tables into one. I had a project in Access with the same requirements and I was able to solve this problem using a query. I was trying to create a query in SQL server similar to what I did in Access, but I don't know how to add a column with formula in SQL Server queries.

I'm also using vb.net for the front-end of my application. So I also need to be able connect what I want to do in DataGridView Any suggestions would be helpful and sorry for any mistakes I'm new in stackoverflow and SQL server.

ACCESS VERSION:

SELECT 
   tblStudent.stud_LRN, 
   tblStudent.stud_Lname, 
   tblStudent.stud_Fname,          
   [tblPerformanceTask]![perf_WS]+[tblQuarterlyAssessment]![quart_WS]+[tblWrittenWork]![writ_WS] AS InitialGrade, 
   IIf([InitialGrade]<60,60+15* ([InitialGrade]/60),75+25*([InitialGrade]-60)/40) AS QuarterGrade,    
   tblStudent.stud_rec_ID
FROM 
   ((tblStudent 
   INNER JOIN tblPerformanceTask ON 
   tblStudent.stud_rec_ID = tblPerformanceTask.Stud_Rec_ID) 
   INNER JOIN tblQuarterlyAssessment ON 
   tblStudent.stud_rec_ID = tblQuarterlyAssessment.stud_rec_ID) 
   INNER JOIN tblWrittenWork ON 
   tblStudent.stud_rec_ID = tblWrittenWork.stud_rec_ID;

SQL SERVER VERSION:

SELECT 
   S.Stud_LRN,
   S.Stud_LName, 
   S.Stud_FName, 
   W.writ_PS, 
   W.writ_WS,    
   P.perf_PS,
   P.perf_WS, 
   Q.quart_PS, 
   Q.quart_WS
FROM 
   tblStudent AS S 
   INNER JOIN tblWrittenWork            AS W ON S.Stud_rec_ID = W.Stud_ID_FK 
   INNER JOIN tblPerformanceTask        AS P ON S.Stud_rec_ID = P.Stud_ID_FK 
   INNER JOIN tblQuarterlyAssessment    AS Q ON S.Stud_rec_ID = Q.Stud_ID_FK

There is no restriction is performing a calculation across columns from multiple tables in a query. If you are trying to create a computed column that is a different story, but in a derived column like you have in your access example you can do the exact same thing in sql server. — Sean Lange
– Sean Lange, Commented Jan 4, 2017 at 18:05
Can you provide more information about the problem you're trying to solve? An example of what you're expecting as a final product might help. I am a bit confused if you just need a select statement, a column (as indicated by the title), or even if a view would serve your purposes. Depending on what you're trying to accomplish will help give us better solutions. — Tor
– Tor, Commented Jan 4, 2017 at 18:14
I'm supposed to use the columns with _PS and _WS for a calculation like Initial = sum of all _WS. — Euphemia
– Euphemia, Commented Jan 4, 2017 at 19:17
@Sean Lange Can you help point me in the right direction? I don't know how to make a derive column in sql server. — Euphemia
– Euphemia, Commented Jan 4, 2017 at 19:18

SqlZim · Accepted Answer · 2017-01-04 18:04:55Z

1

In sql server 2012+ you can also use iif, but here is the case version:

select 
     S.Stud_LRN
   , S.Stud_LName
   , S.Stud_FName
   , W.writ_PS
   , W.writ_WS
   , P.perf_PS
   , P.perf_WS
   , Q.quart_PS
   , Q.quart_WS
   , InitialGrade = P.perf_WS + Q.quart_WS + W.writ_WS
   , QuarterGrade = case 
      when (P.perf_WS + Q.quart_WS + W.writ_WS) < 60
      then 60 + 15 * ((P.perf_WS + Q.quart_WS + W.writ_WS) / 60)
      else 75 + 25 * ((P.perf_WS + Q.quart_WS + W.writ_WS) - 60) / 40
      end
from tblStudent as S
  inner join tblWrittenWork as W 
    on S.Stud_rec_ID = W.Stud_ID_FK
  inner join tblPerformanceTask as P 
    on S.Stud_rec_ID = P.Stud_ID_FK
  inner join tblQuarterlyAssessment as Q 
    on S.Stud_rec_ID = Q.Stud_ID_FK

answered Jan 4, 2017 at 18:04

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create a calculated column using data from other tables in SQL Server 2014?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related