qwerty qwerty - 6 months ago 11
SQL Question

Getting the corresponding column in a different table MySQL

Is there a way where in I could use the sub_name, instead of the sub_id?

This is my code..

echo "<table border=1 align=center><tr class=style2><td>Date<td>Student Name <td> Subject <td> Score";

$sortQuery = mysql_query("select * from mst_adminresult",$cn) or die(mysql_error());

while($row=mysql_fetch_row($sortQuery))
{
echo "<tr class=style8><td>$row[5]<td>$row[1] <td align=center> $row[4] <td align=center> $row[3]/20";
}
echo "</table>";


this code shows a result of all the exam taken. In the
Subject
it's output is the subject ID. I'm just wondering if there is any way I could get its subject name. The subject name is in different table.

adminresult_tbl

CREATE TABLE `mst_adminresult` (
`adminResultID` int(5) NOT NULL,
`login` varchar(20) NOT NULL,
`test_id` varchar(20) NOT NULL,
`score` int(3) NOT NULL,
`test_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


subjecet_tbl

CREATE TABLE `mst_subject` (
`sub_id` int(5) NOT NULL,
`sub_name` varchar(25) DEFAULT NULL,
`sub_desc` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


test_tbl

CREATE TABLE `mst_test` (
`test_id` int(5) NOT NULL,
`sub_id` int(5) DEFAULT NULL,
`test_name` varchar(30) DEFAULT NULL,
`total_que` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


The flow of the program is, when the user takes an exam, the user will be redirected to a page where all the subjects are listed, then the user will choose the subject she wants to take, after that, a list of tests will show, then the user will choose again and that's when the user can have the quiz.

In the list of subjects, I've called the
subject_tbl
. In the list of test, the
test_tbl
. When the user is finished taking the exam, it will store in the
adminresult_tbl

Answer

JOIN the 3 tables. Try this:

SELECT * FROM mst_adminresult m
JOIN test_tbl t ON t.test_id = m.test_id
JOIN subject_tbl s ON s.sub_id = t.sub_id;
Comments