MySQL左联接:从一个表中选择所有内容，但仅将第二个表中的值与条件匹配

问题描述

我有两个桌子.我想从一个表中选择所有内容，从第二个表中选择匹配的行-符合条件.

I have two tables. I want to select everything from one table and the matching row from the second table - that matches a criteria.

tblConfig_Group包含一个组列表.

|ID | groupCode | 
|1  | A         |
|2  | B         |
|3  | C         |
|4  | D         |

tblConfig_CustomerGroup包含这些组中的组和客户的列表.

And tblConfig_CustomerGroup contains a list of groups and customers in that those groups.

|ID | groupCode | customerID |
|1  | A         | 74         |
|2  | B         | 74         |
|3  | A         | 235        |
|4  | C         | 74         |

它应该只显示第二个表中有匹配项的行AND条件(customerID = 74)

It should only display rows from the second table where there is a match AND criteria (customerID = 74)

预期结果:

|ID | groupCode | customerID |
|1  | A         | 74         |
|2  | B         | 74         |
|3  | C         | 74         |
|4  | D         |            |

我尝试了许多查询，但对我来说似乎不起作用...

I've tried a number of queries but it doesn't seem to work for me...

SQL查询1:

SELECT g.groupCode,  cg.customerID FROM tblConfig_Group g 
LEFT JOIN tblConfig_CustomerGroup cg ON g.groupCode = cg.groupCode  
WHERE cg.customerID = '74'

实际结果:第1行，第2行和第3.

ACTUAL RESULTS: rows 1, 2 & 3.

| groupCode | customerID |
| A         | 74         |
| B         | 74         |
| A         | 74         |

SQL查询2:

SELECT g.groupCode,  cg.customerID FROM tblConfig_Group g 
left outer join tblConfig_CustomerGroup cg on g.groupCode = cg.groupCode   
WHERE cg.customerID = '74' 
UNION  
SELECT g.groupCode, cg.customerID 
FROM tblConfig_Group g 
right outer join tblConfig_CustomerGroup cg on g.groupCode = cg.groupCode

实际结果:第1行，第2行和第3 +客户235

ACTUAL RESULTS: rows 1, 2 & 3 + customer 235

| groupCode | customerID |
| A         | 74         |
| B         | 74         |
| A         | 74         |
| A         | 235        |

推荐答案

SELECT 
  g.id,
  g.groupCode, 
  cg.customerID 
FROM tblConfig_Group g 
LEFT JOIN tblConfig_CustomerGroup cg 
ON g.groupCode = cg.groupCode 
  AND cg.customerID = '74';

这篇关于MySQL左联接:从一个表中选择所有内容，但仅将第二个表中的值与条件匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！