title: Is there a way to make Crystal Reports include a constant in a join condition, without using a SQL command object?
body: <p>What I want to do is an outer join to a table, where I exclude records from the joined table based on matching a constant, however keep records from the main table. For example:</p>
  
  <pre><code>SELECT a.id, a.other, b.baz
  FROM a
  LEFT OUTER JOIN b
    ON a.id  = b.id
    AND b.bar = 'foo'
  </code></pre>
  
  <p>Expected results:</p>
  
  <pre>
      id  other       baz      
      --  ----------  -------  
      1   Has foo     Include  
      2   Has none    (null)   
      3   Has foobar  (null)   
  </pre>
  
  <p>I can't get the same results by putting it in the filter condition. If I use the following:</p>
  
  <pre><code>SELECT a.id, a.other, b.baz
  FROM a
  LEFT OUTER JOIN b
    ON a.id  = b.id
  WHERE (b.bar IS NULL OR b.bar = 'foo')
  </code></pre>
  
  <p>I get these incorrect results:</p>
  
  <pre>
      id  other     baz      
      --  --------  -------  
      1   Has foo   Include  
      2   Has none  (null)   
  </pre>
  
  <p>Where it excluded records of A that happen to match a record of B where bar = 'foobar'. I don't want that, I want A to be present, but B to be nulls in that case.</p>
  
  <p>Table B will have multiple records that need excluding, so I don't think I can filter this on the Crystal side without doing a lot of messing around to avoid problems from duplicate records from table A.</p>
  
  <p>I cannot use a SQL command object, as the third party application that we are running the reports from seems to choke on SQL command objects.</p>
  
  <p>I cannot use views, as our support contract does not permit database modifications, and our vendor considers adding views a database modification.</p>
  
  <p>I am working with Crystal Reports XI, specifically version 11.0.0.895. In case it makes a difference, I am running against a Progress 9.1E04 database using the SQL-92 ODBC driver.</p>
  
  <p>The sample tables and data used in the examples can be created with the following:</p>
  
  <pre><code>CREATE TABLE a (id INTEGER, other VARCHAR(32));
  CREATE TABLE b (id INTEGER, bar VARCHAR(32), baz VARCHAR(32));
  insert into A (id, other) values ('1', 'Has foo');
  insert into A (id, other) values ('2', 'Has none');
  insert into A (id, other) values ('3', 'Has foobar');
  insert into B (id, bar, baz) values ('1', 'foo', 'Include');
  insert into B (id, bar, baz) values ('1', 'foobar', 'Exclude');
  insert into B (id, bar, baz) values ('1', 'another', 'Exclude');
  insert into B (id, bar, baz) values ('1', 'More', 'Exclude');
  insert into B (id, bar, baz) values ('3', 'foobar', 'Exclude');
  </code></pre>