title: eSQL multiple join but with conditions
body: <p>I've 3 tables as under</p>
  
  <p>MERCHANDISE     </p>
  
  <pre><code>+-----------+-----------+---------------+
  | MERCH_NUM | MERCH_DIV | MERCH_SUB_DIV |
  +-----------+-----------+---------------+
  |         1 | car       | awd           |
  |         1 | car       | awd           |
  |         2 | bike      | 1kcc          |
  |         3 | cycle     | hybrid        |
  |         3 | cycle     | city          |
  |         4 | moped     | fixie         |
  +-----------+-----------+---------------+
  </code></pre>
  
  <p>PRIORITY                        </p>
  
  <pre><code>+----------+-----------+---------+---------+------------+------------+---------------+
  | CUST_NUM | SALES_NUM | DOC_NUM | BALANCE | PRIORITY_1 | PRIORITY_2 | PRIORITY_CODE |
  +----------+-----------+---------+---------+------------+------------+---------------+
  |       90 |      1000 |      10 |      23 |          1 |          6 | NO            |
  |       91 |      1001 |      20 |      32 |          3 |          7 | PRI           |
  |       92 |      1002 |      30 |      11 |          2 |          8 | LATE          |
  |       93 |      1003 |      40 |      22 |          5 |          9 | 1MON          |
  +----------+-----------+---------+---------+------------+------------+---------------+
  </code></pre>
  
  <p>ORDER                   </p>
  
  <pre><code>+----------+-----------+---------+---------+-----------+-----------+
  | CUST_NUM | SALES_NUM | DOC_NUM | COUNTRY | MERCH_NUM | MERCH_DIV |
  +----------+-----------+---------+---------+-----------+-----------+
  |       90 |      1000 |      10 | INDIA   |         1 | car       |
  |       91 |      1001 |      20 | CHINA   |         2 | bike      |
  |       92 |      1002 |      30 | USA     |         3 | cycle     |
  |       93 |      1003 |      40 | UK      |         4 | moped     |
  +----------+-----------+---------+---------+-----------+-----------+
  </code></pre>
  
  <p>I want to join the left joined table from the last two tables with the first one such that the <code>MERCH_SUB_DIV</code> 'awd' appears only once for each unique combination of <code>merch_num</code> and <code>merch_div</code> </p>
  
  <p>the code I came up with is as under, but I'm not sure how do I eliminate the duplicate row just for the awd</p>
  
  <pre class="lang-sql prettyprint-override"><code>select 
    ROW#, MERCH.MERCH_NUMBER, ORDPRI.MERCH_NUMBER, ORDPRI.CUST_NUM, 
    BALANCE, SALES_NUM, ITEM_NUM, RANK, PRIORITY_1
  from (
    select 
      ROW_NUMBER() OVER(
        PARTITION BY ORD.DOC_NUM, ORD.ITEM_NUM 
        ORDER BY ORD.DOC_NUM, ORD.ITEM_NUM ASC
      ) AS Row#,
      ORD.CUST_NUM, PRI.CUST_NUM, ORD.MERCH_NUM, ORD.MERCH_DIV, PRI.BALANCE,
      pri.DOC_NUM, pri.SALES_NUM, pri.PRIORITY_1, pri.PRIORITY_2 
    from ORDER as ORD 
    left join PRIORITY as PRI on ORD.DOC_NUM = PRI.DOC_NUM 
      and ORD.SALES_NUMBER = PRI.SALES_NUM
    where country_name in ('USA', ‘INDIA’)
  ) as ORDPRI 
  left join MERCHANDISE as MERCH on ORDPRI.DIV = MERCH.DIV 
    and ORDPRI.MERCH_NUM = MERCH.MERCH_NUM
  </code></pre>