sql与mysql中2个表数据进行匹配,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.

So tblConfig_Group contains a list of groups.

|ID | groupCode |

|1 | A |

|2 | B |

|3 | C |

|4 | D |

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 |

It should only display rows from the second table where there is a match

AND criteria (customerID = 74)

EXPECTED RESULTS:

|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 Query 1:

SELECT g.groupCode, cg.customerID FROM tblConfig_Group g

LEFT JOIN tblConfig_CustomerGroup cg ON g.groupCode = cg.groupCode

WHERE cg.customerID = '74'

ACTUAL RESULTS: rows 1, 2 & 3.

| groupCode | customerID |

| A | 74 |

| B | 74 |

| A | 74 |

SQL Query 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

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';