HOW TO USE FULL OUTER JOIN TO JOIN MORE THEN FIVE TABLES

Moses Dwana
Moses Dwana used Ask the Experts™
on
hi guys,
i have four tables (sclass, studentinfo,subject,stugrade). i want to run a query to get data from these four tables. i have run the query to get the data, but the problem is i am only getting data where the tables have field in common. for example, students in (studentinfo table ) don't display if they don't have grade in (stugrade table). i want the students to display even if they don't have grade. i don't know how to apply the full outer join to accomplish this task.

please see what i have done.
	$query="select s.student_id,s.fname,s.mname,s.lname,c.class_id,c.classname,c.sectionname,u.subject_id,u.subjectname,g.period,g.Acadamicyear,g.grade,g.approvedby,g.grade_id from studentinfo s,sclass c,subject u,stugrade g where g.student_id = s.student_id AND g.classid = c.class_id AND g.subject_id = u.subject_id";
	$result = mysqli_query($mysqli, $query);

Open in new window


thanks for the help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
First of all: Use MySQL Workbench for such tasks.
Then: use the newer ANSI SQL JOIN syntax to increase readability.

Your statement:

SELECT *
FROM   studentinfo s ,
       sclass c ,
       subject u ,
       stugrade g
WHERE  g.student_id = s.student_id
       AND g.classid = c.class_id
       AND g.subject_id = u.subject_id;

Open in new window

Using the JOIN syntax reveals better what is already in the WHERE statement:

SELECT *
FROM   stugrade g
       INNER JOIN studentinfo s ON g.student_id = s.student_id
       INNER JOIN sclass c ON g.classid = c.class_id
       INNER JOIN subject u ON g.subject_id = u.subject_id;

Open in new window

Your central table is stugrade. Thus your results.

Solution: You need to organize your query according to your data model. And use OUTER JOIN's instead.

SELECT *
FROM   studentinfo s
       LEFT JOIN stugrade g ON g.student_id = s.student_id
       LEFT JOIN sclass c ON g.classid = c.class_id
       LEFT JOIN subject u ON g.subject_id = u.subject_id;

Open in new window


p.s. using abbreviations in a data model is a bad idea as long as there is not a real reason for it. Especially as it seems that you're using multiple, different ones. This is hard to read and thus hard to understand.

p.p.s. review your data model. Normally a student attends a class. The grade is just a later result on a subject/test.

Author

Commented:
thanks very mush, it is working, but the only problem in you solution is, students that don't have grade information such as class subject, period  acadamy year does not display. i want those information to display as well, only the grade field should be empty if they don't have
ste5anSenior Developer

Commented:
Well, post table DDL. Otherwise it's just guessing..

E.g.

SELECT *
FROM   studentinfo s
       LEFT JOIN sclass c ON s.student_id = c.student_id
       LEFT JOIN subject u ON s.student_id = u.student_id
       LEFT JOIN stugrade g ON s.student_id = g.student_id
                                AND c.class_id = g.class_id
                                AND u.subject_id = g.subject_id;

Open in new window

OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Author

Commented:
i am getting error, the query not running
SELECT *
FROM   studentinfo s
INNER JOIN sclass c ON g.classid = c.class_id
INNER JOIN subject u ON g.subject_id = u.subject_id
LEFT OUTER JOIN stugrade g ON g.student_id = s.student_id

??

Author

Commented:
thanks very mush. thanks for teaching me new tricks, it's working
would you mind closing the question please ?
would you mind closing the question please ?
would you mind closing the question please ?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial