is there proceed clear reports consolidate consistent join condition, but controlling sql management object?

what i wish an outdoor join table, where i bar annals assimilated list formed relating constant, however keep annals sure table. example:

select a.id, a.other, b.baz
from a
left outdoor join b
  a.id  = b.id
  b.bar = 'foo'

expected results:

    id        baz      
    --  ----------  -------  
    1   foo     consolidate  
    2   zero    (null)   
    3   foobar  (null)   

i can't same law putting filter condition. i following:

select a.id, a.other, b.baz
from a
left outdoor join b
  a.id  = b.id
where (b.bar zero b.bar = 'foo')

i crude results:

    id      baz      
    --  --------  -------  
    1   foo   consolidate  
    2   zero  (null)   

where expelled annals occur review record b where bar = 'foobar'. i don't wish that, i wish present, nonetheless b nulls case.

table b have churned annals need excluding, i don't cruise i filter clear side but doing lot messing around prevaricate problems register annals list a.

i can't sql management object, third jubilee concentration controlling reports seems stifle sql management objects.

i can't views, the support agreement does assent database modifications, the businessman considers adding views database modification.

i am operative clear reports xi, personally chronicle 11.0.0.895. box creates difference, i am controlling opposing swell 9.1e04 database controlling sql-92 odbc driver.

the illustration tables information used examples combined following:

create list (id integer, varchar(32));
create list b (id integer, bar varchar(32), baz varchar(32));
insert (id, other) values ('1', 'has foo');
insert (id, other) values ('2', 'has none');
insert (id, other) values ('3', 'has foobar');
insert b (id, bar, baz) values ('1', 'foo', 'include');
insert b (id, bar, baz) values ('1', 'foobar', 'exclude');
insert b (id, bar, baz) values ('1', 'another', 'exclude');
insert b (id, bar, baz) values ('1', 'more', 'exclude');
insert b (id, bar, baz) values ('3', 'foobar', 'exclude');