Have Geek Brain

Selecting a group with the same value but with one exception

I have a table with foreign key, status, code

I would like to select groups with the same foreign key with one record having code 001 and status "incomplete" and everything else should have status "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 is full, 2 is Unknown, and 1 is Incomplete. Then the query should not return this group.

Only if one state is "incomplete" with code 001, and ALL the rest is "completed"

I will be running this in mysql, thanks, appreciate the help.

+1


a source to share


3 answers


I am not familiar with MySQL, but this should be a fairly 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)
+1


a source 


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')


Then you can join this back by t to get the actual data for each group. If there may be several incomplete elements in the group, you need to "select a separate foreign key".

0


a source


You can accomplish this with 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 defines conditions for each group of foreign keys. The first condition says that there should be one line with the 001 code and the Incomplete status. The second condition says that all other lines must be completed.

0


a source






More articles:


All Articles