SELECT data from multiple tables using joins in MySQL

Question

i have follwing tables

USERS :

* id
* name
* role id
* phone

STUDENTS

1: userid ( fk users.id)
2: course_id

STAFF

1: staff_id(fk users.id)
2: course-id

COURSES

1: COURSE_ID
1:COURSENAME

I need to find all accounts associated with a number

I need name,userid,roleid,courseid,corsename,staffid, by providing mobile

i have written this query but this return zero results

SELECT users.name, users.id, staff.user_id, students.course_id, users.role_id, courses.course_title FROM users INNER JOIN students ON users.id = students.user_id INNER JOIN staff ON staff.user_id = users.id INNER JOIN courses ON courses.id = students.course_id WHERE users.phone = '9495990028'

LIMIT 0 , 30

Some of the fields are inconsistent. The fields that you mention each table and the fields that you mention at the end of this question have some little difference. — david
– david, Commented Jul 10, 2018 at 1:31
Please publish at least an attempted solution, demonstrating your effort so far to solve the problem. — dmitryro
– dmitryro, Commented Jul 10, 2018 at 1:32
i am only giving a mobile number.my need is to get details of user. a user may students or teacher. this must join to course table inorder to get course details of a users.this is my requirements — Sreejith sreeji
– Sreejith sreeji, Commented Jul 10, 2018 at 2:55
'SELECT users.name, users.id, staff.user_id, students.course_id, users.role_id, courses.course_title FROM users INNER JOIN students ON users.id = students.user_id INNER JOIN staff ON staff.user_id = users.id INNER JOIN courses ON courses.id = students.course_id WHERE users.phone = '9495990028' LIMIT 0 , 30' — Sreejith sreeji
– Sreejith sreeji, Commented Jul 10, 2018 at 4:11

codeLover · Accepted Answer · 2018-07-10 05:36:25Z

2

Try union operator:

SELECT name, s.userid, roleid, s.course_id, c.coursename, null
FROM USERS u
INNER JOIN STUDENTS s 
INNER JOIN COURSES c
ON s.userid = u.id AND c.course_id = s.course_id
WHERE u.phone = ?
UNION 
SELECT name, null, roleid, s.course_id, c.coursename, s.staff_id
FROM USERS u
INNER JOIN STAFF s
INNER JOIN COURSES c
ON s.staff_id = u.id AND c.course_id = s.course_id
WHERE u.phone = ?;

edited Jul 10, 2018 at 5:36

answered Jul 10, 2018 at 4:22

codeLover

2,5921 gold badge13 silver badges29 bronze badges

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

7 Comments

codeLover Over a year ago

I have listed the columns as per the list mentioned by you. As per the rules of UNION clause both the queries should return same number of columns. However, if you want either userid from student table or staffid from staff table then null can be removed

Sreejith sreeji Over a year ago

ok..i had removed..but u didn't joins the courses table in the above code. i want to know that why did you use AND clause. the query works without AND clause.is this AND make any difference

codeLover Over a year ago

Sorry .. my bad..forgot to put Course table ..but then how did the query work?Anyways, editing the query...

Sreejith sreeji Over a year ago

the what is the need of AND clause..'AND c.course_id = s.course_id' .. it works without AND clause too..pls share

codeLover Over a year ago

You mean to say with ",", how did you try..can you please share the conditionals you tried

|

david · Accepted Answer · 2018-07-10 03:07:19Z

0

For the COURSES table, I check the id with the course_id from students and staff

SELECT name, s.userid, roleid, s.course_id, c.coursename, st.staff_id
FROM USERS u
INNER JOIN STUDENTS s ON s.userid = u.id
INNER JOIN STAFF st ON st.staff_id = u.id
INNER JOIN COURSES c ON c.course_id = s.course_id AND c.course_id = st.course_id
WHERE u.phone = ?

edited Jul 10, 2018 at 3:07

answered Jul 10, 2018 at 1:39

david

3,2289 gold badges33 silver badges43 bronze badges

4 Comments

Sreejith sreeji Over a year ago

no i want to search for the students and staffs for the same mobile number. assume that a teacher is a parent and teaches the same class

david Over a year ago

huh, there is no mobile number field in any table. Please be clearer when asking the question.

david Over a year ago

ah, sorry, you mean the phone field in the USERS, right?

Sreejith sreeji Over a year ago

yes..it's ok. i will my efforts to solve this problem. will post my query so that you can understand what i intended to get

Collectives™ on Stack Overflow

SELECT data from multiple tables using joins in MySQL

2 Answers 2

7 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related