lets say i have a table like this
hr_employee
id name
1 'emp A'
2 'emp B'
3 'emp C'
sale_order
id name employee_id date_order
1 'ORD/01' 1 '2021-07-07'
2 'ORD/02' 2 '2021-08-02'
3 'ORD/03' 3 '2021-08-17'
4 'ORD/04' 3 '2021-08-18'
im using this query:
SELECT
string_agg(s.id::varchar,',') sale_ids,
e.name
from hr_employee e
left join sale_order s on s.employee_id = e.id
where s.date_order between '2021-08-01' and '2021-08-31'
group by e.name
and this is the output
sale_ids name
2 Emp B
4,3 Emp C
i want to have output like this:
sale_ids name
Emp A
2 Emp B
4,3 Emp C
my question is how can i make Emp A appear but shows null in sale_ids fields?
CodePudding user response:
You should move the criteria on the date_order
from the WHERE
clause to the ON
clause of the left join:
SELECT STRING_AGG(s.id::varchar, ',') sale_ids, e.name
FROM hr_employee e
LEFT JOIN sale_order s
ON s.employee_id = e.id AND
s.date_order >= '2021-08-01' AND s.date_order < '2021-09-01'
GROUP BY e.name;
The WHERE
condition is (logically) after the FROM
clause, so the NULL values added by the left join can be eliminated. The ON
clause, on the other hand, is part of the join and cannot exclude rows from hr_employee
.
CodePudding user response:
Filtered out by "date_order",Try to change to "between '2021-07-01' and '2021-08-31'"
CodePudding user response:
SELECT
string_agg(s.id::varchar,',') sale_ids,
e.name
from hr_employee e
left join sale_order s on s.employee_id = e.id
where s.date_order between ('2021-08-01' and '2021-08-31') OR NULL
group by e.name