Join 3 tables and match them column by rows in mysql

[eternalcreator]

I have a database to manage students

1. I want to Select students that are in level 1 fom the students table
2. Select the subjects from the subjects table
3. and echo the matching results on am html table

any help?

[jkon]

You ask help for what ? The title of the topic says that it has something to do with joining three tables , your message doesn't says anything like this.

First about the sql query:

1. Provide the data structure of your tables
2. Provide the criteria that you want the join to be done
3. Give example data of rows in tables and the outcome rows that you want to produce

About how to generate an html view of the rows of the outcome it has nothing to do with your first question. There are many tutorials here but if you have any SPECIFIC problem , describe it and we are here for you

[eternalcreator]

Thanks JSON, i really appreciate this sir.

Below is my table Structure:

This is my goal

AM trying to match students exam score with subjects as Table headers in a view.

it's called broadsheet, as you can see in my goal... James score in ENglish and maths. Writing the sql code to join the three tables that make up that goal has been a hastle. Thanks

[diafol]

There seems to be much duplication in these tables, e.g. subjects and enrol.
I would imagine you want a tables:

students
classes (id | FK: subject_id | FK: teacher_id)
subjects
teachers
enrol (id | student_id | class_id) - no need to duplicate teacher or subject as these are in classes table

What additional fields you need in Enrol is up to you. You may want to have a general performance table:

performance (enrol_id | result | assessment_id)
assessments (id | label (e.g. 1ca, 2ca, exam etc)

Just a loud thought. ANyhow looks like you want a crosstab query of some sort or a PIVOT. Unfortunately, I don't think MySQL has a PIVOT feature. You'll need to write your own - not a simple matter.

[eternalcreator]

Found and Answer:
First,
Thanks to Jkon and Diafol.
I would redesign thse tables for efficiency,... I knew i would have to optmize and normalize those designs

Answer
Fortunately, i found an Article on Mysqli/Pivot with a good example:
https://en.wikibooks.org/wiki/MySQL/Pivot_table
and guess what??? - i only needed one query and like you said those duplicates weren't necessary. I only needed the enrol table to do that and Get Names of the students from the students table

This was the code i ran:

$sql = "select student_id,\n"
    . "sum(exam*(1-abs(sign(subject_id-1)))) as english,\n"
    . "sum(exam*(1-abs(sign(subject_id-2)))) as Mathematics,\n"
    . "sum(exam*(1-abs(sign(subject_id-3)))) as Agric\n"
    . "from enrol WHERE class_id=1 group by student_id";

I am still reading on Pivot table and Cross Tab anyway. There seems to be powerful possibilities there.

Thanks.