title: counting in three joined table
body: <p>I have three tables. The first one is <code>PrivteOwner</code> and has 5 columns (<code>ownerno, fname, lname, address, telno</code>), the second one is <code>PropertyForRent</code> that has 10 columns (<code>propertyno, street, city, postcode, type, rooms, rent, ownerno, staffno, branchno</code>) and the third one is <code>Viewing</code> with 4 columns (<code>clientno, propertyno, viewdate, comment</code>).</p>
  
  <p>I want to find the owner who has the most properties without a viewing. My code is as below: </p>
  
  <pre><code>    SELECT 
          CONCAT (A.fname, ' ', A.lname) AS OwnerName, 
          A.ownerno, B.propertyno, B.ownerno
      FROM 
          PrivateOwner AS A 
      INNER JOIN 
          PropertyforRent AS B ON A.ownerno = B.ownerno
      LEFT JOIN
          viewing AS C
  
     SELECT 
          ownerno, COUNT(ownerno), viewdate
     FROM 
         Max_Property
         GROUP BY ownerno
         ORDER BY COUNT(ownerno) DESC
     WHERE
         ROWNUM = 1 and viewdate IS NULL;
  </code></pre>
  
  <p>Does this code work correctly? If yes how we can write it efficiently? </p>