MYSQL JOIN 在同一张表上 [英] MYSQL JOIN on the same table

问题描述

我目前有这个查询设置:

I currently have this query set-up:

SELECT 
  topic.content_id, 
  topic.title, 
  image.location 
FROM 
  mps_contents AS topic 
  INNER JOIN mps_contents AS image 
    ON topic.content_id = image.page_id 
WHERE 
  topic.page_id = (SELECT page_id FROM mps_pages WHERE page_short_name = 'foo' ) 
  AND image.display_order = '1'

这是因为我想将同一表中的两行合并为一行.这是表格的简化设置

This is because I want to merge two rows from the same table in one row. This is a simplified setup of the table

-----------------------------------------------------------
| page_id | content_id | title | location | display_order |
-----------------------------------------------------------
|    1    |     200    |  Foo  |   NULL   |     200       |
|    1    |     201    |  Baz  |   NULL   |     201       |
|   200   |     201    |  Bar  | jpg.jpg  |      1        |
-----------------------------------------------------------

And basically I want this result

---------------------------------
| content_id | title | location |
---------------------------------
|     200    |  Foo  | jpg.jpg  |
|     201    |  Baz  |   NULL   |
---------------------------------

基本上我想选择所有主题，然后还返回相应的图像(如果有).我当前的查询只返回所有带有关联图像的主题.我尝试了 LEFT 和 RIGHT OUTER JOINS，但似乎没有帮助.

Basically I want to select all topics, then also return the corresponding image if any. My current query only returns all topics with associated images. I tried LEFT and RIGHT OUTER JOINS but it doesn't seem to help.

推荐答案

对 OUTER JOIN 进行筛选时，必须在 ON 子句中或作为派生表进行筛选.当 image.display_order = '1' 在 WHERE 中时，它总是一个 INNER JOIN

When you filter on an OUTER JOIN, you have to filter in the ON clause or as a derived table. When image.display_order = '1' is in the WHERE, it will always be an INNER JOIN

SELECT 
  topic.content_id, 
  topic.title, 
  image.location 
FROM 
  mps_contents AS topic 
  LEFT JOIN
  mps_contents AS image ON topic.content_id = image.page_id
             AND image.display_order = '1'  
WHERE 
  topic.page_id = (SELECT page_id FROM mps_pages WHERE page_short_name = 'foo' ) 

或

SELECT 
  topic.content_id, 
  topic.title, 
  image.location 
FROM 
  mps_contents AS topic 
  LEFT JOIN
  (
   SELECT *
   FROM mps_contents
   WHERE display_order = '1'
  ) AS image ON topic.content_id = image.page_id
WHERE 
  topic.page_id = (SELECT page_id FROM mps_pages WHERE page_short_name = 'foo' ) 

这篇关于MYSQL JOIN 在同一张表上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！