How should I combine three tables using left join

Question

0.00/5 (No votes)

See more:

I have three tables

student
student_id student_name class gender nohp
12345 Lee 5A Male 011-111
54321 Lia 5A Female 022-222

borrow
borrow_id student_id nolaptop lend_date pass_date send_date
1 12345 LP123 01/11/19 NULL 01/10/19
2 54321 LP321 01/11/19 NULL 01/10/19

pass
borrow_id student_id nolaptop lend_date pass_date send_date
1 12345 LP123 01/11/19 02/11/19 01/10/19
NULL NULL NULL NULL NULL NULL

I want the statistic table look(when all of the three tables combine) like this:
student_id student_name class gender nohp borrow_id nolaptop lend_date pass_date send_date
12345 Lee 5A Male 011-111 1 LP123 01/11/19 02/11/19 01/10/19
54321 Lia 5A Female 022-222 2 LP321 01/11/19 NULL 01/10/19

What I have tried:

I use coding:

"select * from borrow left join student on borrow.student_id=student.student_id left join pass on borrow.student_id=pass.sudent_id

but Lia's student_id, borrow_id, nolaptop, lend_date, pass_date and send_date did not show up since all of her data in pass table are null.

Posted 6-Aug-19 19:37pm

rachelamellyn

Updated 6-Aug-19 20:42pm

Add a Solution

Comments

[no name] 7-Aug-19 2:25am

Joining all to student should do the job:

select 
  *
from student 
left join borrow on borrow.student_id = student.student_id 
left join pass on pass.student_id = student.student_id

rachelamellyn 7-Aug-19 4:07am

I already used this coding but still it shows the same result

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2019-08-06T20:42:00

Firstly, show us the actual code you are trying: your code will not work as column pass.sudent_id doesn't exist in your tables.

And when I fix that, it works:

SQL

SELECT * FROM borrow 
LEFT JOIN student ON borrow.student_id=student.student_id 
LEFT JOIN pass ON borrow.student_id=pass.student_id

With your data as shown, I get all the data:

Borrow_ID	Student_ID	NoLapTop	Lend_Date	Pass_Date	Send_Date	Student_ID	Student_Name	Class	Gender	NoHP	Borrow_ID	Student_ID	Lend_Date	NoLapTop	Pass_date	Send_Date
1	12345	LP123     	2019-11-01	NULL	2019-10-01	12345	Lee       	5A        	Male      	011-111   	1	12345	2019-11-01	LP123     	2019-11-02	2019-10-01
2	54321	LP321     	2019-11-01	NULL	2019-10-01	54321	Lia       	5A        	Female    	022-222   	NULL	NULL	NULL	NULL	NULL	NULL

If you want less, then specify exactly which rows you want instead of SELECT *

BTW: your tables appear to be duplicating a lot of data, which is generally a bad idea. And Gender fields should probably be a FOREIGN KEY to a separate table, instead of storing text directly in the "student" table.
I'd look at what you are storing and why, and consider if that's really the best design - duplicated data easily gets out of step and causes major problems later.
I'd also change the names: use plurals for tables, and use ID as the ID row in each table, and reference it via a columns named after the table and "ID":

Students
ID StudentName Class GenderID NoHP

Borrowings
ID StudentID LaptopID LendDate PassDate SendDate

Passes
ID BorrowID StudentID LaptopID LendDate PassDate SendDate