时间戳

首页 > 解决方案 > Return Parameter If in Child Table Meet Some Condition

sql - Return Parameter If in Child Table Meet Some Condition

问题描述

I have table like this

Parent table

id, column1,  etc
-    -         -     
-    -         -

Detail

id, parent_id, column1, actual_finish (value is true/false)
 -     -            -           -      
 -     -            -           -

I want to check if all column actual_finish have value true, then return 1 (I think this will be return parameter), else return 0.

For example

parent

id   column1  etc,
------------------
1    value1    a

Detail

id, parent_id, column1, actual_finish (value is true/false)
------------------------------------------------------------
1       1         a           true
2       1         b           false

This will return 0, because second row actual finish value is false, but if second row column actual_finish updated to true, then return 1

I want to create a stored procedure that returns 0 or 1 based on column actual_finish in the detail table.

Can someone help me?

标签： sqlsql-serverstored-proceduressql-server-2012return-value

解决方案

Query you could use is 

Select returnvalue= case when totalCount=trueCount then 1 else 0 end 
from
(select 
    trueCount=count (case when actual_finish ='true' then 1 else 0 end), 
    totalCount = count(1)
from
parent p left join detail d
    on p.id=d.parent_id
group by p.id
)T

This is assuming that you return false if there is no row in detail table for parent id

推荐阅读