How to always return a row when empty result and using the IN clause with arrays

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.

Method 1

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags:

Leave a Reply