All we need is an easy explanation of the problem, so here it is.
some help needed here. Am using PostgreSQL and given a query like the one bellow (this is parat of a more big query) I need it to ALWAYS return a row.
SELECT content, user_id, date, factor_id, team_id FROM ANSWERS A WHERE A.TEAM_ID IN ('7f4743df-5194-4ba4-8490-4b36ae63e27a', '8a4743df-5194-4ba4-8490-4b36ae63e27a') AND A.DATE >= '2020-10-22'::date AND A.FACTOR_ID IN ('0a577004-b283-410a-b245-bff019a8e70d', '0f577004-b283-410a-b245-bff019a8e54d') ORDER BY USER_ID, DATE
Basically when it finds a combination of team_id T and factor_id F where no results for the given dates, then I want it to return the actuals T and F items, the user_id and date, like this:
(null, ‘0000000-5194-4ba4-8490-000000000’, ‘2020-10-22’, ‘0a577004-b283-410a-b245-bff019a8e70d’, ‘0f577004-b283-410a-b245-bff019a8e54d’)
Is there any way when using the IN clause with array to return the it’s iterating at the moment result was empty?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
You can’t do that with an IN list. You would have to use a VALUES list(s) and LEFT JOIN that to A. Something like:
with b(team_id, factor_id) as (select * from ( values ('7f4743df-5194-4ba4-8490-4b36ae63e27a'), ('8a4743df-5194-4ba4-8490-4b36ae63e27a'))t cross join ( values ('0a577004-b283-410a-b245-bff019a8e70d'), ('0f577004-b283-410a-b245-bff019a8e54d'))f ) SELECT content, user_id, date, b.factor_id, b.team_id FROM b left join ANSWERS A on a.factor_id=b.factor_id and a.team_id=b.team_id and A.DATE >= '2020-10-22';
Now you show non-NULL being returned for user_id and date, but I don’t where you expect them to come from. They can’t from A if there is no row in A to pull them from, and can’t come from the IN/VALUES lists as those don’t contain them those fields.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂