CODEWITHSUNDEEP
mysqlsqlselect
TNK
TNK

Reputation: 4333

Selecting data from 3 tables in MYSQL

I am trying to make a query to get user's name and his/her code_number from 3 tables according to login_id. I have 4 tables named login, tutors, institutes and students.

This is my tables' structure.

login table
login_id
login_type

tutor table
tutor_id
login_id
tutor_name
tutor_code

institute table
institute_id
login_id
institute_name
institute_code

student table
student_id
login_id
student_name
student_code

When an user logged into the site login_id save on SESSION and then I need to check what kind of user has logged in and after identified the user then need to get logged in user's name and code.

My problem is how I check which user (in the case Tutor, institute, student) has logged into site and how can I get its name and code?

Hope someone help me about this... Thank you.

Upvotes: 0

Views: 127

Answers (2)

sgeddes
sgeddes

Reputation: 62861

Here's one way to do it with CASE by checking the Login_Type:

SELECT 
    CASE Login_Type
        WHEN 'Tutor' THEN Tutor_Name
        WHEN 'Institute' THEN Institute_Name
        WHEN 'Student' THEN Student_Name
    END Name, 
    CASE Login_Type
        WHEN 'Tutor' THEN Tutor_Code
        WHEN 'Institute' THEN Institute_Code
        WHEN 'Student' THEN Student_Code
    END Code
FROM Login L
    LEFT JOIN Tutor T ON L.Login_Id = T.Login_Id
    LEFT JOIN Institute I ON L.Login_Id = I.Login_Id
    LEFT JOIN Student S ON L.Login_Id = S.Login_Id

Good luck.

Upvotes: 2

Taryn
Taryn

Reputation: 247880

If you want to return the name and code, then you can just JOIN on the tables. Your query will be similar to this:

select l.login_id,
  l.login_type,
  coalesce(t.tutor_name, i.institute_name, s.student_name) Name,
  coalesce(t.tutor_code, i.institute_code, s.student_code) Code
from login l
left join tutor t
  on l.login_id =t.login_id
left join institute i
  on l.login_id = i.login_id
left join student s
  on l.login_id = s.login_id

The COALESCE will return the first non-null value. If the login can only belong in one of the tables, then using this will return the correct value even if the user is a tutor, institute or student.

If you want to return this in separate columns, then you could use:

select l.login_id,
  l.login_type,
  t.tutor_name,
  t.tutor_code,
  i.institute_name,
  i.institute_code,
  s.student_name,
  s.student_code
from login l
left join tutor t
  on l.login_id =t.login_id
left join institute i
  on l.login_id = i.login_id
left join student s
  on l.login_id = s.login_id

Upvotes: 2

Related Questions