Join Query Help

[shivom]

I am trying to write a query involving 2 tables. The table information is

1)student_application
-student_id
-first_name
-last_name
-title

2)grade
-grade_id
-student_id
-faculty_id
-grade

When student enters the application information it is saved in student_application table.

Than 4 faculty member grade that particular application for that particular student

Right now everything is inserting in the database.

My question is how to display student information with respective grades for all 4 faculty using php and mysql

The form structure and output should be like

first_name | title | faculty_1 | faculty_2 | faculty_3 | faculty_4

test | title | A | B+ | C | A-

I know I have to do join query. But i am new to it.

Please try your best to help me out

[shivom]

I am trying to write a query involving 2 tables. The table information is

1)student_application
-student_id
-first_name
-last_name
-title

2)grade
-grade_id
-student_id
-faculty_id
-grade

When student enters the application information it is saved in student_application table.

Than 4 faculty member grade that particular application for that particular student

Right now everything is inserting in the database.

My question is how to display student information with respective grades for all 4 faculty using php and mysql

The form structure and output should be like

first_name | title | faculty_1 | faculty_2 | faculty_3 | faculty_4

test | title | A | B+ | C | A-

I know I have to do join query. But i am new to it.

Please try your best to help me out

[knucklehead]

This is the query you need:

SELECT * FROM student_application,grade WHERE student_application.student_id = grade.student_id

[scrupul0us]

$sql = 'select * from student_application s inner join grade g on s.student_id = g.student_id';

[shivom]

I tried the query but its not working.

Its shows multiple entries of student name. I want to show student name only once and than the grades of other faculty members

[shivom]

I tried the query but its not working.

Its shows multiple entries of student name. I want to show student name only once and than the grades of other faculty members

[knucklehead]

This query:

SELECT * FROM student_application,grade WHERE student_application.student_id = grade.student_id AND student_application.student_id = '1'

will select all information associated with a student who has ID=1. After that you should be able to print it out however you want 🙂
If you want to display all the students you could do something like this:

$sel1 = "SELECT * FROM student_application";
$all_students = mysql_query($sel1);
$num_students = mysql_num_rows($all_students);

for ($i = 1; $i <= $num_students; $i++) {

$sel2 = "SELECT * FROM student_application,grade WHERE student_application.student_id = grade.student_id AND student_application.student_id = '".$i."'";
$q_student = mysql_query($sel2);

//print it out somehow...

}

Hope this helps... =)

[shivom]

I tried your query but it fails too....The for loop doesnot make any sense....Will you please help me out.

Reason

$sel2 = "SELECT * FROM student_application,grade WHERE student_application.student_id = grade.student_id AND student_application.student_id = '".$i."'";

last part time output is like student_application.student_id = 1 . But i dont have any student application in student_application table.

I appreciate your help

[knucklehead]

How do you think that query "fails"? Please explain =)

That last part of query says to mysql to select student_id from table student_application, not student application from student_application table =)

Please, write me your detailed tables description.

And please explain me what is
faculty_1
faculty_2
faculty_3
faculty_4
from your first post? Where is that information stored?

You're welcome ;-)

[shivom]

Here is the table description

There are 2 tables

1)student_application

-student_id
-first_name
-last_name
-title

2)grade

-grade_id
-student_id
-faculty_id
-grade

when student applies it inserts its detail into student_application tables

for example

1 | first_name | last_name | title

Now grading

There are 4 faculty who are going to grade each student

for example

faculty_1 has faculty_id 11
facutly_2 has faculty _id 22
faculty_3 has faculty_id 33
faculty_4 has faculty_id 44
student has student_id 1
student has student_id 2

so it will insert in database as

grade_id | student_id | faculty_id | grade

1 | 1 | 11 | A
2 | 1 | 22 | B
3 | 1 | 33 | C
4 | 1 | 44 | D
5 | 2 | 11 | A
6 | 2 | 22 | B
7 | 2 | 33 | C
8 | 2 | 44 | D

The final form structure and output should be like

student_name | title | faculty_1 | faculty_2 | faculty_3 | faculty_4

name | title | A | B+ | C | A-

[laserlight]

I think you need to clarify a few things

• Are you trying to retrieve the data for all the students, or only specific students based on student id?

• Are you trying to retrieve the grades assigned by all faculty members involved (i.e., it is given that exactly 4 faculty members grade each student), or only those grades assigned by four specific faculty members (i.e., those with the given faculty member id numbers)?

• Does the order of the displayed student data matter? If so, are you ordering by student name?

• Does the order of the displayed grades matter? If so, how are you ordering it?

[shivom]

1)I am trying to retrieve the data for all the students who are there in student_application table

2)I am trying to retrieve the grade assigned by all (4) faculty members.

3)I am ordering the student data by first name

4)The order of the grade depends on the student name

[laserlight]

shivom wrote:

4)The order of the grade depends on the student name

No, what I mean is the order of the grades for each student as assigned by the different faculty members.

[shivom]

I didnot understand what you are trying to say..

but the order of the grades depends on the student name and faculty

[laserlight]

This is the SQL statement that I would use:

SELECT SA.first_name, SA.last_name, SA.title, G.grade
FROM student_application SA, grade G
WHERE SA.student_id=grade.student_id
ORDER BY SA.last_name, SA.first_name

What happens here is that you will retrieve the results in batches of 4. This allows you to print the 4 grades in one row, then move on to the next student, since the students' data are retrieved in sorted order.