title: Conditional left join on max date and where clause in second table
body: <p>I am attempting to join a customer table with sales table where I show the list of all customers in database and any paid sale the customer might have in the sales tables. Now a customer can have multiple sales rows in the sales table.
  This is an example sales record of one customer with multiple sales in the sale tables</p>
  
  <p><a href="https://i.stack.imgur.com/RfGc1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/RfGc1.png" alt="enter image description here"></a></p>
  
  <p>while extracting this record I would like to get only the MAX (q_saledatetime) WHERE the q_paidamount is > 0.
  <strong><em>as in show me the last time this customer made a payment to us</em></strong>. So in this case row 2 where they paid 8.90 is what I would like to get for that customer. <em>If a customer has no record in the sales table, show their name/details on the list either way.</em></p>
  
  <p>My failure at the moment is how to include the where clause of the paid amount + max date column. </p>
  
  <p>ATTEMPT A</p>
  
  <pre><code>select DISTINCT ON (q_customer.q_code)
  q_customer.q_code, q_customer.q_name,  -- customer info  
  MAX(q_saleheader.q_saledatetime) AS latestDate, q_saleheader.q_paidamount  -- saleheader info
  FROM q_customer
  LEFT JOIN q_saleheader ON (q_customer.q_code =  q_saleheader.q_customercode) 
  group by q_customer.q_code, q_customer.q_name , q_saleheader.q_saledatetime, q_saleheader.q_paidamount
  order by q_customer.q_code ASC
  </code></pre>
  
  <p>which results in </p>
  
  <p><a href="https://i.stack.imgur.com/K2gnE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/K2gnE.png" alt="enter image description here"></a></p>
  
  <p>so for Fred Blogg is picking up details from row 4 instead of 2 <strong><em>(first image)</em></strong>. As there's no rule for q_paidamount at this point</p>
  
  <p>ATTEMPT B</p>
  
  <pre><code>SELECT 
  customer.q_code, customer.q_name,  -- customer info
  sale.q_saledatetime, sale.q_paidamount  -- sale info        
  FROM q_customer customer
  LEFT JOIN (SELECT * FROM q_saleheader WHERE q_saledatetime = 
  (SELECT MAX(q_saledatetime) FROM q_saleheader b1 where q_paidamount &gt; 0 )) 
  sale ON sale.q_customercode = customer.q_code
  </code></pre>
  
  <p>which results in </p>
  
  <p><a href="https://i.stack.imgur.com/rWn9B.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/rWn9B.png" alt="enter image description here"></a></p>
  
  <p>This doesnt seem to be getting any information from the sale table at all.</p>
  
  <p><strong><em>Update:</em></strong></p>
  
  <p>After having a closer look at my first attempt I amended the statement and came up with this solution which achieves the same results as Michal's answer. I just curious to know is there any pitfalls or perfomance disadvantages with the following way.</p>
  
  <pre><code>select DISTINCT ON (q_customer.q_code)
  q_customer.q_code, q_customer.q_name,  -- customer info  
  q_saleheader.q_saledatetime, q_saleheader.q_paidamount  -- saleheader   info
  FROM q_customer
  LEFT JOIN q_saleheader ON (q_customer.q_code =   q_saleheader.q_customercode AND 
  q_saleheader.q_paidamount &gt; 0 ) 
  group by q_customer.q_code, q_customer.q_name ,  q_saleheader.q_saledatetime, 
  q_saleheader.q_paidamount
  order by q_customer.q_code ASC, q_saleheader.q_saledatetime DESC 
  </code></pre>
  
  <p>main change was adding AND q_saleheader.q_paidamount > 0 on the join and q_saleheader.q_saledatetime DESC to make sure are getting the top row of that related data. As mentioned both Michal's answer and this solution achieve the same results. Just curious about pitfalls in either of the two ways.</p>