Drupal database and SQL I have done lot of things with Drupal 7. Now I have something to do that can't be done with view interface (in my opinion) and I need to ues php and SQL. Now I am testing in view php field. 

I have many node types, i.e. students, past_study etc., with many custom fields. 

the first problem I faced was the name of table, I though "node type name" i.e. students, past_study should be use in FROM but after lot of hours wasting I discover each filed has its own table so have to open so many table like this 

FROM field_data_field_this, field_data_field_that

Now the main problem is with WHERE clause I have student_code in students node type and a reference field s_code_ref field in past_study. I am not sure how to compere student_code in student node type with s_code_ref. 

WHERE student_code = s_code_ref


when s_code_ref is a reference entity field in past_study

What I am doing is to adding a field with complicated calculation from the field of two node types and have to add this in a view with various other field from two node types. 

my SQL is something like this 

&lt;?php
$result = db_query ('
  SELECT SUM((pstd.field_studyDate_value2 - pstd.field_studyDate_value) + (std.field_joiningDate_value - now()))  AS duration 
  FROM field_data_field_past_study pstd, field_data_field_student std

  ');


std has single record for each student, and pstd may have 0 or more records for students.
students name, code, etc are being displaying in the view with relationship of two node type. 

my problem is that how to refer the student_code in record line displaying in view and then using that in where clause to calculate the sum for that particular student_code.

so I need something like 

WHERE pstd.s_code_ref = the student_code of the current row.


besides I want to know if my approach to calculate this way is right or there is other better ways?

in short I have to calculate total past study duration from one node type (past_study) then add duration of current study from another node type (student) and bringing the total study duration in a field for each student in a view. 

Any help guidance will be appreciated.
Solved

Finally I solved the problem with following codes 

$eid = $row-&gt;id; // a variable to store row id 
$sql = "SELECT SUM(field_past_std_date_value2 - field_past_std_date_value) as psd, field_std_pin_target_id as tid 
FROM field_data_field_pas_std_date d JOIN field_data_field_std_pin p ON d.entity_id = p.entity_id 
WHERE field_std_pin_target_id = $eid"; 


this bring me the total duration of past study of each student listed in my view. Although the problem is solved there may be better way to writer above squl. if anyone can give comments it will be highly appreciated. I'll continue improving my Drupal skills.