JOIN 3 TABLES AND SHOW ALL DATA OF FIRST TABLE

Codes here !

Moderators: macek, egami, gesf

Post Reply
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

Post by 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
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

Post by 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
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

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

Post Reply

Who is online

Users browsing this forum: No registered users and 2 guests