Question

我有多个MySQL表格,有不同的栏目。在加入三个表格之后,我有一个表格,其结构如下:

+------------+------------+-----------+-------+------+
| student_id | first_name | last_name | class | rank |
+------------+------------+-----------+-------+------+
| 1          | John       | Doe       | 2012  | 1    |
+------------+------------+-----------+-------+------+
| 2          | Suzy       | Public    | 2013  | 12   |
+------------+------------+-----------+-------+------+
| 3          | Mike       | Smith     | 2014  | 50   |
+------------+------------+-----------+-------+------+

I also have two additional tables that aren t involved in the initial join:

<>光>

+-------------+------------+-----------------------+----------------+
| interest_id | student_id | employer_interest     | interest_level |
+-------------+------------+-----------------------+----------------+
| 1           | 1          | Wayne Enterprises     | High           |
+-------------+------------+-----------------------+----------------+
| 2           | 1          | Gotham National Bank  | Medium         |
+-------------+------------+-----------------------+----------------+
| 3           | 2          | Wayne Enterprises     | Low            |
+-------------+------------+-----------------------+----------------+
| 4           | 3          | Gotham National Bank  | High           |
+-------------+------------+-----------------------+----------------+

<><>>>>>

+----------+------------+-----------------------+
| offer_id | student_id | employer_offer        |
+----------+------------+-----------------------+
| 1        | 1          | Wayne Enterprises     |
+----------+------------+-----------------------+
| 2        | 1          | Gotham National Bank  |
+----------+------------+-----------------------+
| 3        | 2          | Wayne Enterprises     |
+----------+------------+-----------------------+

The interest and offers table won t necessarily contain a record for every student_id but at the same time contain multiple records that reference a single student_id.

For each of the latter two tables, I d like to:

Select all rows where the employer_interest or employer_offer value is equal to $var (a variable I ve set in PHP)
Join these rows to the original table

For example, if $var is set to Wayne Enterprises, I d like the resulting table to be:

+------------+------------+-----------+-------+------+-------------------+----------------+-------------------+
| student_id | first_name | last_name | class | rank | employer_interest | interest_level | employer_offer    |
+------------+------------+-----------+-------+------+-------------------+----------------+-------------------+
| 1          | John       | Doe       | 2012  | 1    | Wayne Enterprises | High           | Wayne Enterprises |
+------------+------------+-----------+-------+------+-------------------+----------------+-------------------+
| 2          | Suzy       | Public    | 2013  | 12   | Wayne Enterprises | Low            | Wayne Enterprises |
+------------+------------+-----------+-------+------+-------------------+----------------+-------------------+
| 3          | Mike       | Smith     | 2014  | 50   | NULL              | NULL           | NULL              |
+------------+------------+-----------+-------+------+-------------------+----------------+-------------------+

我是否试图仅仅利用MySQL的提问来做什么? 如果是的话,我如何这样做?

Answer 1

如同你一样,它只是需要其他表格的LEFT JOIN,因为看看看一看,看看一看所有学生,不管他们是否愿意工作。

If so... ensure both the "Interest" and "Offers" tables have an index where the student ID is either a single element index, or first in that of a compound index.

select STRAIGHT_JOIN
      ORS.Student_ID,
      ORS.First_Name,
      ORS.Last_Name,
      ORS.Class,
      ORS.Rank,
      JI.Employer_Interest,
      JI.Interest,
      OFR.Employer_Offer
   from 
      OriginalResultSet ORS

         LEFT JOIN Interest JI
            ON ORS.Student_ID = JI.Student_ID
           AND JI.Employer_Interest = YourPHPVariable

            LEFT JOIN Offers OFR
               on JI.Student_ID = OFR.Student_ID
              AND JI.Employer_Interest = OFR.Employer_Offer

为了防止“NUL”在雇主的利益、兴趣和意愿方面产生结果,你可以把“NUL”列为“Coalesce”(关于左边的所有三个栏)

COALESCE( JI.Employer_Interest, " " ) Employer_Interest

Answer 2

你的问问:

select 
    s.student_id, s.first_name, s.last_name, s.class, s.rank, 
    i.employer_interest, i.interest_level, 
    o.employer_offer 
from students s
left join interest i 
    on i.student_id = s.student_id 
    and i.employer_interest =  Wayne Enterprises 
left join offers o 
    on o.student_id = s.student_id 
    and o.employer_offer =  Wayne Enterprises

友情链接