nads nads - 6 months ago 7
SQL Question

MySQL Left Joins: Select everything from one table, but only matching value in second table with criteria

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 |

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