CODEWITHSUNDEEP
mysql
Brad
Brad

Reputation: 12262

need to check other table if field in table has 1 in it

I have three tables

students: first_name, last_name, high_school_id, other_high_school

high_schols: id, title

other_high_schools: id, title, student_id

I want to display a list of each student with the following info: first name, last name, high school

The high_schools table contains a pre-filled in list of high schools a student can choose from, if they do not find theirs, they fill in their high school in the "other high school" field. When the student submits the form, their info gets stored, setting students.other_high_school field to 1 and stores their id (student.id = other_high_school.student_id), with the title of their high school into the other_high_schools table (other_high_school.title).

select 
 first_name, last_name, hs.title as high_school
from
 students s
left join
 high_schools hs
on
 s.high_school_id = hs.id

That returns first_name, last_name, high_school, but is it possible to modify that query to detect if students.other_high_school = 1, then join the other_high_school and not the high_schools table?

This does not work, but should help explain what I am trying to accomplish:

select 
 first_name, last_name, hs.title as high_school
from
 students s

CASE s.other_high_school
 WHEN 0 THEN
  left join
   high_schools hs
  on
   s.high_school_id = hs.id
 WHEN 1 THEN
  left join
   other_high_school hs
  ON
   s.id = hs.student_id
 ELSE
  left join
   other_high_school hs
  ON
   s.id = hs.student_id
END CASE

SOLVED

select 
 first_name, last_name, 
 IF(s.other_high_school = 1, ohs.title, hs.title) high_school
from
 students s
left join
 high_schools hs
on
 s.high_school_id = hs.id
left join
 other_high_schools ohs
on
 s.id = ohs.student_id

Upvotes: 1

Views: 63

Answers (1)

liquorvicar
liquorvicar

Reputation: 6106

Firstly, I would reconsider your schema. Do you really need high_schools and other_high_schools in different tables? Or could you have one table with an extra flag as to whether it was a default high school or a user-added high school?

Secondly, I have a feeling your solution will not scale very well as you are LEFT JOINing on all three tables. As the tables grow, I suspect performance will degrade quite quickly. I would suggest an alternative:

SELECT first_name, last_name,title
FROM (
SELECT first_name, last_name,title
FROM students s
    INNER JOIN high_schools hs ON s.high_school_id = hs.id
WHERE s.other_high_school=0
UNION
SELECT first_name, last_name,title
FROM students s
    INNER JOIN other_high_schools ohs ON s.high_school_id = ohs.id
WHERE s.other_high_school=1
) AS combined_schools
ORDER BY last_name,first_name

With the right INDEXes this should be as quick as your solution and will probably scale better (but this does all depend on the shape of your real data set). I've added an ORDER BY to show how you might manipulate the combined results.

Upvotes: 1

Related Questions