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

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
Moses DwanaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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.
Moses DwanaAuthor 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 DeveloperCommented:
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

CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Moses DwanaAuthor Commented:
i am getting error, the query not running
skullnobrainsCommented:
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

??
Moses DwanaAuthor Commented:
thanks very mush. thanks for teaching me new tricks, it's working
skullnobrainsCommented:
would you mind closing the question please ?
skullnobrainsCommented:
would you mind closing the question please ?
skullnobrainsCommented:
would you mind closing the question please ?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.