Click here to Skip to main content
14,267,453 members
Rate this:
Please Sign up or sign in to vote.
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
Updated 6-Aug-19 20:42pm
Comments
0x01AA 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

Rate this:
Please Sign up or sign in to vote.

Solution 1

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:
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
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100