why coalesce returns Scalar sub-query has returned multiple rows even though the values are distinct - SQL

Question

I have 2 tables that I want to join. These tables are kp.pi and g.gu

kp.pi joins with g.gu based on pi.xx_id = gu.yy_id and on pi.xx_type = gu.yy_type, and I want to get the userid field from g.gu table after this join.

However, in some cases, because the type (i.e. pi.xx_type = gu.yy_type) doesn't match in both tables, it will return null for userid. Hence, I want to exclude this join condition to get the userid from g.gu if the types doesn't match.

So the query will be the same, just excluding the pi.xx_type = gu.yy_type join condition to get userid from g.gu.

I tried to do this using coalesce but somehow keep getting the error:

Scalar sub-query has returned multiple rows even though the userid values are distinct in the g.gu table.

Could anyone see whats wrong with my query below pls?:

  select submission_id, xx_id, coalesce (userid ,
                  (select distinct userid from 
                 ( SELECT *
                 from kp.pi
                 where source like '%abc%'
                 and id in (123,
                 456,
                 7877
                 )
                 ) pi

                  left join

                  (select  yy_id,
                        id as userid,
                         case
                         when type = 1 then 'bb'
                         when type = 2 then 'cc'
                         when type = 3 then 'dd' end as yy_type
                 from g.gu)
                 on xx_id = yy_id
                 )
                 )
                  as test_userid              
                 from
                 (select
                 id as submission_id,
                 xx_id,
                 xx_type
                 from kp.pi
                 where source like '%abc%'
                 and id in (123,
                 456,
                 7877
                 )
                 ) pi

                 left join

                 (select yy_id,
                  id as userid,
                  case
                         when type = 1 then 'bb'
                         when type = 2 then 'cc'
                         when type = 3 then 'dd' end as yy_type
                 from g.gu   
                 ) gu
                 on pi.xx_id = gu.yy_id
                and pi.xx_type = gu.yy_type

Answer 1

Without sample of representative data assumptions about the data need to be made. I believe you are trying to give priority to the userid that has the matching pi.xx_type = gu.yy_type but if that isn't available to use userid where that condition isn't met. Instead of trying to place this logic into the join, I suggest using row_number() over(partition by id order by case when type in (1,2,3) then 1 else 2 end) this will give each gu.id a row number of 1, and due to the ordering used, that row number will be amongst the types 1,2,3 if available otherwise to the next available type. Then, by using this row number in the join condition you can only get 1 row per gu.id and that row will be prioritised by matching type/unmatched type.

SELECT
      pi.id AS submission_id
    , pi.xx_id
    , gu.userid AS test_userid
FROM kp.pi
LEFT JOIN (
    SELECT
          yy_id
        , id AS userid
        , CASE 
            WHEN type = 1 THEN 'bb'
            WHEN type = 2 THEN 'cc'
            WHEN type = 3 THEN 'dd'
            END AS yy_type
        , row_number() over(partition by id 
                            order by case when type in (1,2,3) then 1 else 2 end) as rn
    FROM g.gu
    ) gu ON pi.xx_id = gu.yy_id AND gu.rn = 1
WHERE pi.source LIKE '%abc%'
    AND pi.id IN (123, 456, 7877)

If this does not solve the problem (e.g. you do want types aa and bb and cc for each gu.id if available) then please provide sample data from each table to fully represent your issue.