Selecting a group with the same value but with one exception

Question

Hi,

I have a table with a foreign key, status, code

I would like to select the groups with the same foreign key with one record having a code of 001 and status of 'incomplete' and all the rest HAS to have a status of 'completed'

id   foreignkey                    code                 status
---------------------------------------------------------------------
01 ---  04   -------------         009   ---------    completed 
02 ---  04   -------------         009   ---------    completed 
03 ---  04   -------------         009   ---------    completed 
04 ---  04   -------------         009   ---------    completed 
05 ---  04   -------------         009   ---------    completed 
06 ---  04   -------------         009   ---------    completed 
07 ---  04   -------------         009   ---------    completed 
08 ---  04   -------------         001   ---------    incomplete

Let's say that foreign key "04" has 8 records where 5 has a status of complete, 2 has a status of 'Unknown' and 1 with 'Incomplete'. Then the query should not return this group.

Only if one status is 'incomplete' with code of 001 and ALL the rest is 'completed' status

I'll be running this in mysql, Thanks, Appreciate the help.

Answer 1

I'm not familliar with MySQL, but this should be reasonably general syntax -

select * from table
  where status in ('completed','incomplete') 
   and foreignkey in (
                     select foreignkey 
                     from table 
                     where code='001' 
                      and staus='incomplete' 
                     group by foreignkey 
                     having count(*) =1)

Answer 2

select t.foreignkey
from t
where t.code = '001' and not exists (
  select 1
  from t t2
  where t2.foreignkey = t.foreignkey and t2.id <> t.id and t2.code <> '009')

You could then join this back onto t to get the actual data for each group. If there may be multiple incomplete items in a group, you need "select distinct foreignkey".

Answer 3

You can accomplish this with a GROUP BY:

select foreignkey
from yourtable
group by foreignkey
having sum(case when code='001' and status='incomplete' then 1 else 0 end) = 1
and sum(case when status='completed' then 1 else 0 end) = count(*) - 1

The HAVING clause specifies conditions per foreign key group. The first condition says that there must be one row with code 001 and status Incomplete. The second condition says that all the other rows must be completed.