JOIN 3 TABLES AND SHOW ALL DATA OF FIRST TABLE

[fanjava]

Dear all master,

I made application for report an assessment that have 3 tables, mapel(course subject), nilai(course value) and siswa(student).

I want to show all course subject and filter by student ID, and if there are no course subject in course value, it always show all course subject but with 0 course value.

I try to use JOIN TABLE, but if i add WHERE clause, it not work like i want.

May be you can view my PHP Code and MySQL Code.
PHP FILE :
http://pastebin.com/aHjes1tX

SQL FILE :
http://pastebin.com/ZGtB0p1E

Please help me,

Thanks before, master

[Nullsig]

You need to use "INNER JOIN"

Code: Select all

$query = "SELECT * FROM mapel as M INNER JOIN nilai as N ON N.id_mapel=M.id_mapel WHERE N.id_siswa='1'";

This says show all columns in mapel and nilai where id_mapel exists in nilai AND id_siswa = '1'

[fanjava]

Hello, thank you for your fast response.
But i want to show all mapel although in nilai the id_mapel not exist, and it shows by id_siswa

Thanks ..

[Nullsig]

Code: Select all

$query = "SELECT * FROM mapel as M LEFT OUTER JOIN nilai as N ON N.id_mapel=M.id_mapel AND N.id_siswa='1'";

[fanjava]

Hello, Nullsig thanks for your help. It works for me!
But can you explain that SQL for me? I want to understand it.

Thank you!

[Nullsig]

The LEFT OUTER JOIN is used to guarantee that every row in the table to the left of the join will be displayed even if a successful join to the table on the right doesn't exist. [RIGHT OUTER JOIN works in the opposite direction]

The ON portion of the query chooses how to join the two tables. If you use the ON like I did it will show all rows in Mapel regardless if there is a nilai record to mach. Further more it will also show all rows in Mapel regardless if there is a id_siswa match.

When you used the WHERE clause to filter the id_siswa it applied that filter to the entire result set instead of just limiting the join.



The general order of operations for a SQL query is as follows:
-Each JOIN is executed in the order it is declared.
-Each ON clause is executed as each JOIN resolves to filter the join
-The WHERE clause then is executed filtering the table data resulting from all the joins
-The GROUP BY statement is then applied grouping the filtered result set
-Next the "HAVING" is applied to the Grouped result set
-Next the SELECT fields are processed from left to right.
-Last the ORDER BY statement resolves.