使用LEFT JOIN仅选择一个连接的行 [英] Using LEFT JOIN to only selection one joined row

问题描述

我正在尝试左联接两个表，以获取TABLE_1中所有行的列表以及TABLE_2中一个相关行的列表.我已经尝试过LEFT JOIN和GROUP BY c_id，但是我不想将TABLE_2中的相关行按isHeadOffice DESC排序.

I'm trying to LEFT JOIN two tables, to get a list of all rows from TABLE_1 and ONE related row from TABLE_2. I have tried LEFT JOIN and GROUP BY c_id, however I wan't the related row from TABLE_2 to be sorted by isHeadOffice DESC.

这是一些示例表

TABLE 1
c_id    Name
----------------
1       USA
2       Canada
3       England
4       France
5       Spain

TABLE2
o_id    c_id    Office              isHeadOffice
------------------------------------------------
1       1       New York            1
2       1       Washington          0
3       1       Boston              0
4       2       Toronto             0
5       3       London              0       
6       3       Manchester          1
7       4       Paris               1
8       4       Lyon                0

所以我想从中得到的是这样的:

So what I am trying to get from this would be something like:

RESULTS
c_id    Name        Office
----------------------------
1       USA         New York
2       Canada      Toronto
3       England     Manchester
4       France      Paris
5       Spain       NULL

我正在使用PHP& MySQL的.有什么想法吗?

I'm using PHP & MySQL. Any ideas?

推荐答案

SELECT  *
FROM    table1 t1
LEFT JOIN
        table2
ON      o.id = 
        (
        SELECT  o_id
        FROM    table2 t2
        WHERE   t2.c_id = t1.c_id
        ORDER BY
                t2.c_id DESC, t2.isHeadOffice DESC, t2.o_id DESC
        LIMIT 1
        )

在table2 (c_id, isHeadOffice, o_id)上创建索引以使其快速运行.

Create an index on table2 (c_id, isHeadOffice, o_id) for this to work fast.

子查询中的ORDER BY子句似乎是多余的，但是MySQL选择正确的索引是必需的.

The ORDER BY clause in the subquery may seem redundant but it is required for MySQL to pick the right index.

这篇关于使用LEFT JOIN仅选择一个连接的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！