title: How to avoid duplicates in creating view with Left Join if a joining column in one table can have duplicates?
body: <p>I need to create a view from 2 tables.  They are linked by one column, but in one table this column is a primary key, in another table this column can have duplicates.   The resulting view should not have duplicates for this column.  If a row in the 2nd table meets CASE condition then the result in the view should be 'Y' regardless on other rows for the same key.</p>
  
  <p>Please, see the scrip and the join results.  <strong>The 1st row in the results is incorrect, it should be eliminated</strong></p>
  
  <pre><code>Create table A1Table(
      a Integer not null
  );
  
  Create table A2Table(
      a Integer,
      b Integer
  );
  
  insert into A1Table values (1);
  insert into A1Table values (2);
  insert into A1Table values (3);
  insert into A2Table values (1, 1);
  insert into A2Table values (1, 100);
  
  CREATE VIEW A12 AS select a1.a,
  CASE
      WHEN a2.b=100 AND a2.b is not NULL THEN 'Y'
      ELSE 'N'
  END AS report
  FROM A1Table a1
  Left JOIN A2Table a2 ON a2.a = a1.a;
  </code></pre>
  
  <p>--></p>
  
  <pre><code>   a | report
    ---+--------
   **1 | N** -&gt; *should be eliminated*
     1 | Y
     2 | N
     3 | N
  </code></pre>