MySQL Select Join on Multiple Tables

Question

I have multiple tables (tbldept, tblcourse, tblstud, tblviolation) and I want to extract specific values. The below tables is like the same on my tables

tbldept
id | dept
1  | deptA
2  | deptB

tbldept has foreign key on tblcourse

tblcourse
id | deptId | course
1  |    2     | courseA
2  |    1     | courseB
3  |    1     | courseC

tblcourse has foreign key on tblstud

tblstud
id | courseId | name 
1  |   1      | studA
2  |   2      | studB
3  |   1      | studC

tblstud has foreign key on tblviolation

tblviolation
id | studId | violationName
1  |   3    |  violationA
2  |   2    |  violationB
3  |   1    |  violationC
4  |   3    |  violationC

*What I want to get is look like this: *

dept | studId | name   | violationName
  2  |    1   | studA  | violationC
  2  |    2   | studB  | violationB
  1  |    3   | studC  | violationA
  1  |    3   | studC  | violationC

I want to get all the rows of tblviolation for each studId. I hope you guys understand what I am trying to explain. =) Thank you.

FWIW, I find the convention of sticking 'tbl' in front of everything absolutely infuriating. What's wrong with 'departments', 'students', and 'courses'? — Strawberry
– Strawberry, Commented Sep 20, 2017 at 5:27

Utsav · Accepted Answer · 2017-09-20 02:59:11Z

1

You just need inner join. Try this.

select d.dept,s.studid,s.name,v.violationname
from tbldept d 
inner join tblcourse c
on d.id=c.deptid
inner join tblstud s
on c.id=s.courseid
inner join tblviolation v
on s.id=v.studid

answered Sep 20, 2017 at 2:59

Utsav

8,1632 gold badges22 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Select Join on Multiple Tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related