sql, oracle, database

Question

I had two tables I have joined them. Now, I need to select from that joined table 2 columns and one should be the column which if case has both defendant and plaintiffs then it should display OK, if only defendant then def, plaintiff then pltf, if none of them then none.

I basically have this kind of table:

Personid#    Case#       Role        CaseType
----------------------------------------------
cg902        CB190       Plaintiff   Civil
cg903        CB190       Defendant   Civil
cg904        CB191       Plaintiff   Civil
cg905        CB192       Defendant   Civil
cg906        CB193        none       Civil

I need this:

Case#       ANYCOLNAME    CaseType
----------------------------------
CB190       OK            Civil
cg904       PLTF          Civil
cg905       Def           Civil
cg906       None          Civil

I would be really grateful.

Answer 1

replace table_name with your table name
This query would solve your requirements:

select distinct y.case#,
case 
when x.role='Plaintiff' and y.cnt=1 then 'PLTF'
when x.role='Defendant' and y.cnt=1 then 'Def'
when x.role='none' and y.cnt=1 then 'NONE'
when y.cnt=2 then
  case
  when exists(select 1 from TABLE_NAME where role='Plaintiff' and CASE#=y.case#) and
  exists(select 1 from TABLE_NAME where role='Defendant' and CASE#=y.case#)
  then 'OK'
  end
end 
from 
(select case#,
count(case#) as cnt
from TABLE_NAME
group by case#
order by case#) y, TABLE_NAME x
where x.case#=y.case#
order by y.case#
;

Answer 2

Something like this (with fixed case and personID issue):

SQL> with test (ccase, crole) as
  2    (select 'cb190', 'plaintiff' from dual union
  3     select 'cb190', 'defendant' from dual union
  4     select 'cb191', 'plaintiff' from dual union
  5     select 'cb192', 'defendant' from dual union
  6     select 'cb193', 'none'      from dual
  7    ),
  8  inter as
  9    (select ccase, min(crole) minrole, max(crole) maxrole
 10     from test
 11     group by ccase
 12    )
 13  select ccase,
 14    case when minrole <> maxrole then 'ok'
 15         when minrole =  maxrole and minrole = 'plaintiff' then 'pltf'
 16         when minrole =  maxrole and minrole = 'defendant' then 'def'
 17         else 'none'
 18    end anycolname
 19  from inter
 20  order by ccase;

CCASE ANYC
----- ----
cb190 ok
cb191 pltf
cb192 def
cb193 none

SQL>