仅连接值与变量匹配的特定行

发布于 12-08 05:02 字数 3395 浏览 0 评论 0原文

我有多个包含不同数量列的 MySQL 表。连接三个表后,我得到一个结构如下的结果表:

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

我还有两个未参与初始连接的附加表:

interest

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

offers

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

interestoffers 表不一定包含每个 student_id 的记录,但同时包含引用单个 的多个记录学生 ID

对于后两个表中的每一个,我想:

  1. 选择 employer_interestemployer_offer 值等于 $var 的所有行(我在 PHP 中设置的变量)
  2. 将这些行连接到原始表

例如,如果 $var 设置为 Wayne Enterprises,我希望结果表为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 查询就可以做到吗?如果是这样,我该怎么做?

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

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

offers

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

  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

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

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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

千年*琉璃梦2024-12-15 05:02:59

听起来您只需要左连接到其他表,因为您似乎希望看到第一组中的所有学生,无论任何工作机会/兴趣如何。

如果是这样...确保“兴趣”和“优惠”表都有一个索引,其中学生 ID 是单个元素索引,或者是复合索引的第一个索引。

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() 调用中,例如(对于左连接上的所有三列)

COALESCE( JI.Employer_Interest, " " ) Employer_Interest

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.

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

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
情深已缘浅2024-12-15 05:02:59

您的查询应该是这样的:

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'

Your query should be something like this:

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'
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文