Help in using inner join to join three tables

[punji]

I have three tables namely student, result and subject but whenever i try to join them i get this error message in the browser
Database query failed: Unknown column 'student.student_code' in 'field list'
here is my query

$query = "SELECT student.student_code,
student.firstname, 
student.middlename, 
student.lastname, 
student.gender, 
result.student_code, 
result.subject_code, 
result.mark, 
subject.subject_code, 
subject.subject_name ";
$query .= " FROM student AS s ";
$query .= " INNER JOIN result AS r ON s.student_code=r.student_code";
$query .= " INNER JOIN subject AS m ON m.subject_code=r.subject_code";
$query .= " WHERE student_code = '{$session_id}' ";
$query .= " LIMIT 1";

And here are the table structure

    This is student table

    student_code    int(255) primary key
    firstname   varchar(250)
    middlename  varchar(250)
    lastname    varchar(250)        
    gender  varchar(250)
    school_code varchar(250)    

    This is result table

    student_code    int(255)
    subject_code    int(10)
    mark    int(10) 

    This is subject table  

    subject_code    int(10)
    subject_name    varchar(250)

[hericles]

Because you've chosen to name the table in the FROM clause ('FROM student AS s'), you need to use that name in your selection part of the query
SELECT s.student_code...

[punji]

i still get the same problem

[edbr]

from mysql

SELECT t1.name, t2.salary
  FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;

SELECT t1.name, t2.salary
  FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;

so maybe is the AS m part

[punji]

well i tried and i got this error
Database query failed: Column 'student_code' in where clause is ambiguous

[edbr]

try s.student_code then

[JerrimePatient]

select desired_field1, desired_field1, desired_fieldn from table1 t1 inner join (tabl2 t2, table3 t3, table4 t4) on (t1.t2ID=t2.t2ID and t1.t3ID=t3.t3ID and t1.t4ID=t4.t4Id)

[JerrimePatient]

$query = "SELECT s.student_code,
    s.firstname, 
    s.middlename, 
    s.lastname, 
    s.gender, 
    r.student_code, 
    r.subject_code, 
    r.mark, 
    sb.subject_code, 
    sb.subject_name 
    FROM student s 
    INNER JOIN (result r, subject sb) on 
        (s.student_code =r.student_code and 
        r.subject_code = sb.subject_code)";

[punji]

thanks jerimepatient i tried with your query it works but if i add the where clause it says Database query failed: Column 'student_code' in where clause is ambiguous

[edbr]

specify student_code, s.student_code

[JerrimePatient]

$query = "SELECT s.student_code,
    s.firstname, 
    s.middlename, 
    s.lastname, 
    s.gender, 
    r.student_code, 
    r.subject_code, 
    r.mark, 
    sb.subject_code, 
    sb.subject_name 
    FROM student s 
    INNER JOIN (result r, subject sb) on 
        (s.student_code =r.student_code and 
        r.subject_code = sb.subject_code)
        WHERE s.student_code = '". $student_code ."'";

ambiguous means you have two or more tables that contains student_code and those tables are student and result.
You didn't specified the table you want to get the data.

For this situation I use student so the where data for student_code is s.student_code.

[punji]

well i did that but i get nothing, only table header, here is my full code

<strong><table align="center" border="1" width="100%">
<tr>
<th>Subject Code</th>
<th>Subject Name</th>
<th>Marks</th>
</tr>
<?php
$query = "SELECT s.student_code,
    s.firstname, 
    s.middlename, 
    s.lastname, 
    s.gender, 
    r.student_code, 
    r.subject_code, 
    r.mark, 
    sb.subject_code, 
    sb.subject_name 
    FROM student s 
    INNER JOIN (result r, subject sb) ON 
        (s.student_code =r.student_code AND 
        r.subject_code = sb.subject_code)";
$query .= " WHERE s.student_code = '{$student_code}' ";
$query .= " LIMIT 1";   
$sql= mysql_query($query);
while($row=mysql_fetch_array($sql))
{
    ?>
    <tr>
    <td><p><?php echo $row($_SESSION['subject_code']); ?></p></td>
    <td><p><?php echo $row($_SESSION['subject_name']); ?></p></td>
    <td><p><?php echo $row($_SESSION['mark']); ?></p></td>
    </tr>
    <?php
}
?>

[JerrimePatient]

line 22: $query .= " WHERE s.student_code = '{$student_code}' ";

change it to : $query .= " WHERE s.student_code ='" . $student_code . "'";

[punji]

sorry brother did that but only table header appear on browser and no any error

[JerrimePatient]

line 29 - 31: 
<td><p><?php echo $row($_SESSION['subject_code']); ?></p></td>
<td><p><?php echo $row($_SESSION['subject_name']); ?></p></td>
<td><p><?php echo $row($_SESSION['mark']); ?></p></td>

change it to:
<td><p><?php echo $row['subject_code']; ?></p></td>
<td><p><?php echo $row['subject_name']; ?></p></td>
<td><p><?php echo $row['mark']; ?></p></td>

remove $_SESSION[]

[punji]

sorry brother i have chenged it and still nothing

[punji]

<strong><table align="center" border="1" width="100%">
<tr>
<th>Subject Code</th>
<th>Subject Name</th>
<th>Marks</th>
</tr>
<?php
$query = "SELECT s.student_code,
    s.firstname, 
    s.middlename, 
    s.lastname, 
    s.gender, 
    r.student_code, 
    r.subject_code, 
    r.mark, 
    sb.subject_code, 
    sb.subject_name 
    FROM student s 
    INNER JOIN (result r, subject sb) ON 
        (s.student_code =r.student_code AND 
        sb.subject_code = r.subject_code)";
$query .= " WHERE s.student_code = '".$student_code."'";
$query .= " LIMIT 1";   
$sql= mysql_query($query);
while($row=mysql_fetch_array($sql))
{
    ?>
    <tr>
    <td><p><?php echo $row['subject_code']; ?></p></td>
    <td><p><?php echo $row['subject_name']; ?></p></td>
    <td><p><?php echo $row['mark']; ?></p></td>
    </tr>
    <?php
}
?>

[JerrimePatient]

what is the value of $student_code?
or did you store a value in $student_code?

[punji]

numbers, for now it's 1 to 101

[JerrimePatient]

try this:
$query .= " WHERE s.student_code = '1'";

[punji]

yes student_code store student unique number which starts from 1 to 101