I have two tables: tblCompany and tblCompanyAddress.
I'm looking for a MS Access SQL query for showing 2 columns:
- Column1 --> All Cname from tblCompany
- Column2 --> Cadr from tblCompanyAddress if Catyp = 1 or show Null (if not exist or Catyp <>1)
tblCompany:
| id | Cname |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
tblCompanyAddress:
| id | tblCompanyKey | Cadr | Catyp |
|---|---|---|---|
| 1 | 1 | po | 1 |
| 2 | 1 | kk | 2 |
| 3 | 3 | dd | 2 |
| 4 | 3 | er | 3 |
| 5 | 4 | er | 1 |
Expected result
| id | Cname | Cadr |
|---|---|---|
| 1 | A | po |
| 2 | B | |
| 3 | C | |
| 4 | D | er |
Can anyone help?
Best regards
CodePudding user response：
The LEFT JOIN garantees that all rows from company are present.
To get only Cadr from Ctype 1 you need in Access an Subselect
SELECT
tblCompany.* ,tblCompanyAddress.[Cadr]
FROM tblCompany
LEFT JOIN (SELECT * FROM tblCompanyAddress WHERE tblCompanyAddress.Catyp = 1) AS tblCompanyAddress ON tblCompany.ID = tblCompanyAddress.tblCompanyKey
Resuls in
