SQL MAX, more JOINS and WHERE

Question

I have tried to find a solution in other question, but no luck yet. So I try it this way: I have 3 tables:

table: w_header:

|event_perfno_i|state|reg |issue_date|closing_date|
|--------------|-----|----|----------|------------|
|1111111       |C    |AAA |13-1-2019 |13-1-2019   |
|1111112       |C    |AAA |14-1-2019 |14-1-2019   |
|1111113       |C    |BBB |14-1-2019 |14-1-2019   |
|1111114       |C    |CCC |13-1-2019 |13-1-2019   |
|1111115       |C    |CCC |14-1-2019 |14-1-2019   |

table: w_header_crx

|event_perfno_i|check|
|--------------|-----|
|1111111       |Y    |
|1111112       |Y    |
|1111113       |Y    |
|1111114       |Y    |
|1111115       |Y    |

table ac:

|reg    |oper   |status|
|-------|-------|------|
|AAA    |CLK    |0     |
|BBB    |CLK    |0     |
|CCC    |CLK    |0     |
|DDD    |CLK    |0     |
|EEE    |CLK    |0     |

With showing the needed fields and using below query its working fine. But if I add other fields, the outcome is not correct and it is showing too much record.

SELECT w_header.reg, MAX(w_header.event_perfno_i) AS WO
FROM w_header
LEFT JOIN ac ON w_header.reg = ac.reg
JOIN w_header_crx ON w_header_crx.event_perfno_i =   w_header.event_perfno_i
WHERE (ac.ac_typ IN ('E17', 'E19'))
AND ac.oper = 'CLK'
AND w_header.state = 'C'
AND w_header_crx.check = 'Y'
GROUP BY 
w_header.reg
ORDER BY w_header.reg

The SQL does give more records as it should be, despite the conditions.

The expected output should be:

|event_perfno_i|reg |issue_date |closing_date|
|--------------|----|-----------|------------|
|1111112       |AAA |14-1-2019  |14-1-2019   |
|1111113       |BBB |14-1-2019  |14-1-2019   |
|1111115       |CCC |14-1-2019  |14-1-2019   |

Hope my wish is clear, thx.

Answer 1

if you want only the AC present the you should use INNER JOIN and not LEFT JOIN

SELECT w_header.reg, MAX(w_header.event_perfno_i) AS WO
FROM w_header
INNER JOIN ac ON w_header.reg = ac.reg
INNER JOIN w_header_crx ON w_header_crx.event_perfno_i =   w_header.event_perfno_i
WHERE (ac.ac_typ IN ('E17', 'E19'))
AND ac.oper = 'CLK'
AND w_header.state = 'C'
AND w_header_crx.check = 'Y'
GROUP BY  w_header.reg
ORDER BY w_header.reg