CODEWITHSUNDEEP
sqloracle-database
Drew
Drew

Reputation: 113

Conditional column value, Select

I got 2 tables "Records" and "Char". With 1 -> N relation I need to make a select, with a subquery/join where the value to present on the join column is a fixed string like "Multiple Chars" or the content Char.char_val

Let me illustrate:

Records: 

     R_ID | Name        Char: C_ID | R_ID | Char_Val
        1    A                 1        3     c1
        2    B                 2        1     c2
        3    C                 3        1     c3
                               4        2     c3

Expected Result: 

       R_ID | Name  |   Char_Val
        1    A      Multiple Records
        2    B              c3
        3    C              c1

I guess my query would be something like:

Select r.R_ID, r.Name, (conditional select) Char_Val
From Records r, Char c
where r.R_ID = c.R_ID

Suggestions for the (conditional select)?

Upvotes: 0

Views: 197

Answers (3)

Alex Poole
Alex Poole

Reputation: 191570

You can use a case statement and aggregation to get a fixed string:

case when count(c.c_id) > 1 then 'Multiple Records' else max(c.char_val) end

and you need to group by r_id and name:

select r.r_id, r.name,
  case when count(c.c_id) > 1 then 'Multiple Records'
    else max(c.char_val) end as char_val
from records r
join char c on r.r_id = c.r_id
group by r.r_id, r.name
order by r.r_id;

I've also switched to use ANSI joins instead of the old syntax (as @Thorsten suggested).

This is a demo using CTE to generate your data, giving them slightly different names because char is a reserved word:

with t_records (r_id, name) as (
  select 1, 'A' from dual
  union all select 2, 'B' from dual
  union all select 3, 'C' from dual
),
t_char (c_id, r_id, char_val) as (
  select 1, 3, 'c1' from dual
  union all select 2, 1, 'c2' from dual
  union all select 3, 1, 'c3' from dual
  union all select 4, 2, 'c3' from dual
)
select r.r_id, r.name,
  case when count(c.c_id) > 1 then 'Multiple Records'
    else max(c.char_val) end as char_val
from t_records r
join t_char c on r.r_id = c.r_id
group by r.r_id, r.name
order by r.r_id;    

      R_ID N CHAR_VAL        
---------- - ----------------
         1 A Multiple Records
         2 B c3              
         3 C c1

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

Group by r_id. Either MIN = MAX or you want 'Multiple Records':

select r_id, r.name, c.char_vals
from
(
  select 
    r_id,
    case when min(char_val) = max(char_val) then min(char_val) else 'Multiple Records' end 
      as char_vals
  from char
  group by r_id
) c
join records r using(r_id)
order by r_id;

Upvotes: 1

Ram Limbu
Ram Limbu

Reputation: 442

Following query gives the result (with Char_val separated by comma) you expected:

Select r.R_ID, r.Name, listagg(c.char_val,',') WITHIN GROUP(ORDER BY c.char_val) AS Char_Val
From Records r, Char c
where r.R_ID = c.R_ID
GROUP BY r.R_ID, r.Name

Upvotes: 1

Related Questions