2

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?

0

1 Answer 1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.