Joining two SQL queries

Question

I have a site that queries teachers' profiles when the user searches for a subject. So, for example, if I search for math, I get all the teachers who teach math -- but unfortunately, I don't see the other subjects that they teach, because my where condition only includes math. I am currently using:

SELECT *
        FROM subjects
        INNER JOIN teachers_subjects ON
        subjects.subject_id = teachers_subjects.subject_id
        INNER JOIN users_teachers ON teachers_subjects.users_user_id = users_teachers.user_id
        INNER JOIN users ON users_teachers.user_id = users.user_id
        WHERE subjects.subject_en_name ='math'

This will get me teacher John, but it will only tell me that he teaches math, when in fact he actually also teaches science and english.

These two SQL queries work. The first:

SELECT users.user_id
        FROM subjects
        INNER JOIN teachers_subjects ON
        subjects.subject_id = teachers_subjects.subject_id
        INNER JOIN users_teachers ON teachers_subjects.users_user_id = users_teachers.user_id
        INNER JOIN users ON users_teachers.user_id = users.user_id
        WHERE subjects.subject_en_name ='math'

This will return to me a user_id that I can then plug into this:

SELECT *
        FROM subjects
        INNER JOIN teachers_subjects ON
        subjects.subject_id = teachers_subjects.subject_id
        INNER JOIN users_teachers ON teachers_subjects.users_user_id = users_teachers.user_id
        INNER JOIN users ON users_teachers.user_id = users.user_id
        WHERE users.user_id = 100

Which gives me all the information that I need about John, including the fact that he teaches math, science and english, although my first query only asked about math. I would like to know how I can combine these two queries, so I don't have to query the database twice upon every search.

UDPATE

Thanks to @DRapp, I think this may be working:

SELECT
  * 

  from
  ( SELECT distinct
          ts.users_user_id
       from
          subjects s
             JOIN teachers_subjects ts
                ON s.subject_ID = ts.subject_id
       where
          s.subject_en_name = 'math' ) qualified
     JOIN users u  
        ON qualified.users_user_id = u.user_id
     JOIN users_teachers ut
        ON qualified.users_user_id = ut.user_id
     JOIN teachers_subjects ts2    
        ON qualified.users_user_id = ts2.users_user_id
        JOIN subjects s2 
           ON ts2.subject_id = s2.subject_id

Answer 1

Start first with WHO qualifies for teaching the class in question (math)

SELECT distinct
      ts.users_user_id
   from
      subjects s
         JOIN teachers_subjects ts
            ON s.subject_ID = ts.subject_id
   where
      s.subject_en_name = 'math'

So this will give a unique Teacher's USER ID that QUALIFIES... Now, consider you want any teacher who teaches math OR English. Just change the where clause from = 'math' to IN ('math', 'English'). It will still only give you ONE instance of a teacher even if they teach both or just one or the other.

Now, to get the rest of what you want. From this list of qualified teachers for the subject(s) IN question, now get the rest OF the details... wrap this into another query as... I try to never use wild-cards for many purposes, so I am just guessing at some column names in the users table associate for getting teachers information.

SELECT
      qualified.users_user_id,
      u.TeacherName,
      u.TeacherEmail,
      ts2.OtherInformationThatMayBeAvailable,
      s2.subject_id,
      s2.subject_en_name,
      s2.anyOtherSubjectBasedFields
   from
      ( SELECT distinct
              ts.users_user_id
           from
              subjects s
                 JOIN teachers_subjects ts
                    ON s.subject_ID = ts.subject_id
           where
              s.subject_en_name = 'math' ) qualified
         JOIN users u     <-- this join to get teacher name info
            ON qualified.users_user_id = u.user_id
         JOIN teachers_subjects ts2    <-- this to now get ALL classes per teacher qualified
            ON qualified.users_user_id = ts2.users_userID
            JOIN subjects s2   <-- for subjects details that the teacher TEACHES
               ON ts2.subject_id = s2.subject_id

BE SURE TO STRIP OUT MY NOTES via <-- comment at the JOIN component in the query

So, if a teacher qualified for math, but also teaches science and English, this will return 3 records for that one teacher. If another only teaches math, one record for them... and another teacher math and social studies, then two for that person.

Again, this includes all the unique subject details that you may want to grab from the subjects table and/or teachers subjects.

IF you don't care about all the specific details, then you do a group by the user id of the teacher and do a "group_concat( s2.subject_en_name, ',' ) " and it would build a list per teacher record like "math, science, English"