DB Query. How to capture 'mixed' status on a single row

I have a database query ...

select foo, bar, status 
    from mytable 
    where bar in (bar1, bar2, bar3);

The status is a status associated with the pair foo-bar. The GUI display is going to display 1 row fo开发者_C百科r every foo, and should display a checked-checkbox if for all bar1, bar2, bar3 for that foo, the status are all 1. And an unchecked chceckbox if for that foo, the status values of bar1, bar2 and bar3 are all zero. If, again for a given foo, different bars have a different status, I am required to display some other token (a questionmark, say.)

My knowledge of sql isn't sufficient to this task. Can this be done in sql. it's in Oracle, if that makes a difference. I'm thinking I may have to suck it into perl and check for the condition there, but I'm not happy with that idea.

In T-SQL I'd do this:

create table mytable (foo nvarchar(128), bar nvarchar(128), status int)
go
select foo, (MAX(status) + MIN(status)) as status 
from mytable 
group by foo

then in the client app the resulting status value will be 0 if all are unchecked, 1 if some checked, and 2 if all checked

With a CTE to supply sample data, different combinations of zeros and ones in the row statuses gives different output values:

with tmp_tab as (
    select 'foo1' as foo, 'bar1' as bar, 0 as status from dual
    union
    select 'foo1' as foo, 'bar2' as bar, 0 as status from dual
    union
    select 'foo1' as foo, 'bar3' as bar, 0 as status from dual
    union
    select 'foo2' as foo, 'bar1' as bar, 0 as status from dual
    union
    select 'foo2' as foo, 'bar2' as bar, 1 as status from dual
    union
    select 'foo2' as foo, 'bar3' as bar, 0 as status from dual
    union
    select 'foo3' as foo, 'bar1' as bar, 1 as status from dual
    union
    select 'foo3' as foo, 'bar2' as bar, 1 as status from dual
    union
    select 'foo3' as foo, 'bar3' as bar, 1 as status from dual
)
select foo,
    case
        when sum(status) = 0 then 'Unchecked'
        when sum(status) = count(bar) then 'Checked'
        else 'Unknown'
    end as status
from tmp_tab
where bar in ('bar1','bar2','bar3')
group by foo;

FOO  STATUS
---- ---------
foo1 Unchecked
foo2 Unknown
foo3 Checked

perhaps restructure your query to perform a union instead of the IN.

in that way, you will have an explicit value (unique to each unioned select statement, that will tell you which value has been matched.

If the designer were smart, bar1, bar2, and bar3 should be numeric powers of 2, so one can apply bitwise operators to them - then it would be trivial to know which of the particular bars are set.

I'm assuming bar1/2/3 can only be 0 or 1:

SELECT foo, bar,
       CASE WHEN ( bar1 + bar2 + bar3 = 3 ) THEN 'checked'
            WHEN ( bar1 + bar2 + bar3 = 0 ) THEN 'unchecked'
            ELSE 'something else' END
  FROM mytable 
 WHERE bar in (bar1, bar2, bar3);

Or am I missing something?

Edit: Looks like I originally misunderstood the problem. I think this will work.

SELECT foo,
       DECODE( sum_status, 0, 'unchecked', 3, 'checked', 'something else' )
  FROM ( SELECT foo, SUM( status ) AS sum_status
           FROM mytable
          WHERE bar in (bar1, bar2, bar3)
          GROUP BY foo )

Again, this assumes status can only be 0 or 1.