JOIN 3 TABLES AND SHOW ALL DATA OF FIRST TABLE

Codes here !

Moderators: macek, egami, gesf

User avatar
fanjava
New php-forum User
New php-forum User
Posts: 3
Joined: Thu Dec 08, 2011 12:05 am
Location: Jakarta, Indonesia
Contact:

JOIN 3 TABLES AND SHOW ALL DATA OF FIRST TABLE

Postby fanjava » Wed May 23, 2012 8:36 am

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, :help:

Thanks before, master

User avatar
Nullsig
php-forum Fan User
php-forum Fan User
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: JOIN 3 TABLES AND SHOW ALL DATA OF FIRST TABLE

Postby Nullsig » Wed May 23, 2012 8:50 am

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'

User avatar
fanjava
New php-forum User
New php-forum User
Posts: 3
Joined: Thu Dec 08, 2011 12:05 am
Location: Jakarta, Indonesia
Contact:

Re: JOIN 3 TABLES AND SHOW ALL DATA OF FIRST TABLE

Postby fanjava » Thu May 24, 2012 12:32 am

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 ..

User avatar
Nullsig
php-forum Fan User
php-forum Fan User
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: JOIN 3 TABLES AND SHOW ALL DATA OF FIRST TABLE

Postby Nullsig » Thu May 24, 2012 5:09 am

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'";

User avatar
fanjava
New php-forum User
New php-forum User
Posts: 3
Joined: Thu Dec 08, 2011 12:05 am
Location: Jakarta, Indonesia
Contact:

Re: JOIN 3 TABLES AND SHOW ALL DATA OF FIRST TABLE

Postby fanjava » Thu May 24, 2012 6:46 am

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

Thank you!

User avatar
Nullsig
php-forum Fan User
php-forum Fan User
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: JOIN 3 TABLES AND SHOW ALL DATA OF FIRST TABLE

Postby Nullsig » Thu May 24, 2012 9:00 am

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.


Return to “mySQL & php coding”

Who is online

Users browsing this forum: Bing [Bot] and 2 guests