Null values in table while cross join

[rahulroshan]

Hi I am trying to cross join 3 tables and use the values from this to calculate cumulative Accuracy for each student.

But I am missing some students name when they dont have values in one of the tables.

Eg:

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

// to have the student names stored in array

$qr="SELECT DISTINCT(student) from binnerslisttranslit order by student ASC";

$result1=mysql_query($qr);

$re=1;
while($field1=mysql_fetch_array($result1))
{


 $student[$re]=$field1['student'];
       $re++;
       
       }

$ke=$re;

for ($re=1;$re<$ke;$re++)
{






$query="SELECT  student1,Accuracy1,Accuracy2,Accuracy3,(Accuracy1+Accuracy2+Accuracy3)/3 as Accuracy 
FROM
(SELECT student,COUNT(id) as samplesize2,SUM(scores) as Cumulative2,(100-((SUM(scores)/COUNT(id))*100)) as Accuracy2 from  tWeek2Feb2012  where student='$student[$re]'  group by student)
T1 CROSS JOIN
(SELECT student as student1,COUNT(id) as samplesize1,SUM(scores) as Cumulative1,(100-((SUM(scores)/COUNT(id))*100)) as Accuracy1 from  tWeek1Feb2012   where student='$student[$re]'  group by student)
T2 CROSS JOIN
(SELECT COUNT(id) as samplesize3,SUM(scores) as Cumulative1,(100-((SUM(scores)/COUNT(id))*100)) as Accuracy3 from  tWeek3Feb2012   where student='$student[$re]'  group by student)T3;";

$result = mysql_query($query);

// to store student accuracy for each weeks stored in array
while($row = mysql_fetch_array($result))
{

$student[$re]=$row['student1'];

$Accuracy1[$re]=$row['Accuracy1'];

$Accuracy2[$re]=$row['Accuracy2'];
$Accuracy3[$re]=$row['Accuracy3'];
$Accuracy[$re]=$row['Accuracy'];

?>



<table  align='center' border="1" bgcolor="White" cellspacing="2" cellpadding="9">


<tr>
<td> <?php echo $re; ?> </td>
<td> <?php echo $student[$re]; ?> </td>
<td> <?php echo $Accuracy1[$re]; ?>  </td>

<td> <?php echo $Accuracy2[$re]; ?> </td>

<td> <?php echo $Accuracy3[$re]; ?> </td> 

<td> <?php echo $Accuracy[$re]; ?>   </td>

</tr> 


</table>

<?php
}




}

Thanks

[StephNicolaou]

Are the fields blank or do they have the value zero in them?

[rahulroshan]

fields are blank

[smantscheff]

Submit a test case with CREATE TABLE and INSERT statements which shows the problem with your query.

[rahulroshan]

sorry I didn't get u . can u explain more

[smantscheff]

A test case is a series of SQL statements which create tables, fill them with data and contain the query which poses a problem for you.
It typically consists of
- one or more CREATE TABLE statements to set up the tables,
- several INSERT INTO statements to fill them with test data, and
- a query (THE query) which you have problems with.