仅对值与变量匹配的特定行进行联接 [英] Join only for specific rows where value matches a variable

问题描述

我有多个包含不同数量列的MySQL表.加入三个表后，我得到一个结果表，其结构如下:

I have multiple MySQL tables containing varying numbers of columns. After joining three of the tables, I have a resulting table that's structured as follows:

+------------+------------+-----------+-------+------+
| 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     |
+----------+------------+-----------------------+

interest和offers表不一定包含每个student_id的记录，但是同时包含引用单个student_id的多个记录.

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.

对于后两个表，我想:

1. 选择employer_interest或employer_offer值等于$var(我在PHP中设置的变量)的所有行
2. 将这些行加入到原始表中

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

例如，如果$var设置为Wayne Enterprises，我希望结果表为:

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查询就是想做的事情吗?如果是这样，我该怎么办?

Is what I'm trying to do possible using just a MySQL query? If so, how do I do it?

推荐答案

听起来您只需要向其他表格左移即可，因为看来您希望看到第一组的所有学生，而不管任何工作/职位/兴趣.

it sounds like you just need a LEFT JOIN to the other tables since it appears you want to see all students from the first set regardless of any job offer/interest.

如果是这样...请确保兴趣"表和要约"表都具有一个索引，其中学生ID是单个元素索引，还是复合索引中的第一个.

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

为防止"NULL"导致雇主产生兴趣，兴趣和要约，您可以将它们包装在Coalesce()调用中，例如(对于左联接的所有三列)

To prevent "NULL" results in the employer interest, interest and offer, you can wrap them in a Coalesce() call such as (for all three columns on left join)

COALESCE( JI.Employer_Interest, " " ) Employer_Interest

这篇关于仅对值与变量匹配的特定行进行联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！