SQL query left join issue

Question

I am making a query but it is not working properly.

My table details are as follows:

Subarea:
id
Fieldsofstudy

student_subarea:
id primary key,
student_id ,
student_subarea Foreign key to subarea id and the student_subarea.

ASK:
What I want to accomplish is to obtain all fields of study in one column and in another column the id of the student if he is in the class. Otherwise, show null or something.

SELECT a.`id` ,  a.`name` , a.`area_id` , u. * 
FROM  `subarea` a
LEFT JOIN student_subarea u ON u.subarea_id = a.id
WHERE u.student_id =50
OR u.student_id IS NULL

Doing this is not helping at all. I tried to use functions and subqueries without any success. Could some help me.

You might want to update your answer to include what kind of SQL server you are using, as well as any error messages you're getting when you try to run the code that you supplied. — kbuilds, Jun 12 '16 at 16:43
I am not getting any error. I am just selecting in the wrong data over and over and over and I don't know how to get what I said before. — lostintheriver, Jun 12 '16 at 16:45

Gordon Linoff · Accepted Answer · 2016-06-12T23:13:19.320

2

The general rule for left join and filtering is to put the filtering clauses in the on clause for all but the first table. So this may do what you want:

SELECT a.`id`, a.`name`, a.`area_id`, u. * 
FROM `subarea` a LEFT JOIN
     student_subarea u
     ON u.subarea_id = a.id AND u.student_id = 50;

How do you remember this logic? A left join returns all rows from the first table even when there is no match on the second table. That appears to be what you want.

The problem with your logic is that students other than student 50 match the logic. So, those rows get filtered out.

edited Jun 12 '16 at 23:13

answered Jun 12 '16 at 16:45

Gordon Linoff

1,242,037
58
646
786

Thank you Gordon. Good explanation! – lostintheriver Jun 12 '16 at 16:56

score 0 · Answer 2 · answered Jun 12 '16 at 16:45

0

Move the filter criteria to the JOIN

SELECT a.`id` ,  a.`name` , a.`area_id` , u. * 
FROM  `subarea` a
LEFT JOIN student_subarea u 
  ON u.subarea_id = a.id
     AND u.student_id =50

answered Jun 12 '16 at 16:45

AgRizzo

5,261
1
13
28

score 0 · Answer 3 · answered Jun 12 '16 at 16:45

0

You should put the condition on the student ID inside the join condition, not in the where clause:

SELECT    a.id,  a.name , a.area_id, u.* 
FROM      subarea a
LEFT JOIN student_subarea u
       ON u.subarea_id = a.id
      AND u.student_id = 50

answered Jun 12 '16 at 16:45

trincot

317,000
35
244
286

SQL query left join issue

3 Answers3