How to combine tables using SQL Joins?

Question

I have these three tables:

Student

student_id 012345
name       Lee
class      5A
gender     Male
nohp       011-1111111

Laptop

idborrow    5
student_id  012345  
no_laptop   LP12345
lend_date   01/08/2019
pass_date   NULL
send_date   01/11/2019

Pass

idborrow    5
student_id  012345
no_laptop  LP12345
lend_date  01/08/2019 
pass_date  01/08/2019   
send_date  01/11/2019

I want to join the table in statistic (if the teacher didn’t approve):

student_id  012345 
name        Lee   
class       5A    
gender      Male    
nohp        011-1111111    
idborrow    5      
no_latop   LP12345 
lend_date  01/08/2019
pass_date  NULL  
send_date  01/11/2019

And if the teacher approve him, it would be like this:

student_id  012345 
name        Lee   
class       5A    
gender      Male    
nohp        011-1111111    
idborrow    5      
no_latop   LP12345 
lend_date  01/08/2019
pass_date  01/08/2019  
send_date  01/11/2019

I use the coding:

$query=”select * from student 
        inner join book on student.student_id=book.student_id”

However, it only showed table that pass_date was null. And if I use:

$query=”select * from student 
        inner join book on student.student_id=book.student_id 
        inner join pass on book.student_id=pass.student_id”

It only show if it have data on pass table but didn’t show up if the pass table is null.

I agree you have to look at your joins, but please realize that apart from that your question makes little sense. You have three tables, of which two seem very similar, then you explain something, who knows what, and finially, in your real question, there's a fourth table. What? — KIKO Software, Aug 05 '19 at 06:43

score 0 · Answer 1 · answered Aug 05 '19 at 06:41

0

This is because, you are using INNER JOIN. Please read up this article. You should use either LEFT JOIN or FULL OUTER JOIN, depending on how exactly you want the data.

answered Aug 05 '19 at 06:41

Martin Dimitrov

1,304
1
11
25

score 0 · Answer 2 · answered Aug 05 '19 at 06:43

0

You can use left join instead of inner join. difference of join types

answered Aug 05 '19 at 06:43

Sibel Genç

54
3

score 0 · Answer 3 · answered Aug 05 '19 at 06:53

0

You got to use left join on tables which are master tables (carrying majority of data) as in your case pass. This would mean when pass left join...other tables is done, the pass table's data both matched amd unmatched data would be the output.

whereas,

Inner Join gives the data which only matches with other tables data. pass innerjoin other tables will be only matched data from both the tables but no unmatched data (when you apply left to join it gives unmatched data of left table + matched data of both tables likewise right as vice versa)

answered Aug 05 '19 at 06:53

Himanshu

3,830
2
10
29

ok lets say all of the datas on pass table are null, i use the coding: $query="select * from borrow left join student on borrow.student_id=student_id left join pass on borrow.student_id=student.student_id". My student_id,idborrow, no_laptop, lend_date, pass_date, send_date become null eventhough it already have data on borrow table – rachela69 Aug 06 '19 at 06:58

How to combine tables using SQL Joins?

3 Answers3