title: MySQL sorting the result using a field of a different table
body: <p>I have 3 tables of the following structure</p>
  
  <pre><code>    products
      {
          pid
          pname
          plocation...
      }
  
      services
      {
          s_id
          s_name
          s_location...
      }
  
      txn
      {
          tid
          pid...
      }
  </code></pre>
  
  <p>I am using the following query to get the values I require.</p>
  
  <pre><code>    $dbquery = "SELECT DISTINCT products.pid AS id,
                     products.pname AS name,
                     products.p_desc AS description,
                     products.p_loc AS location,
                     products.category AS category,
                     products.p_uid AS userid,
                     products.add_date AS dateadded,
                     products.isaproduct AS whatisit
                FROM products
               WHERE products.pname
                LIKE '%".$keyword."%'
                 AND category = '".$refine_value."'
               UNION
               SELECT DISTINCT services.s_id AS id,
                     services.s_name AS name,
                     services.s_desc AS description,
                     services.s_category AS category,
                     services.s_uid AS userid,
                     services.s_location AS location,
                     services.date_added AS dateadded,
                     services.isaservice AS whatisit
                FROM services
               WHERE services.s_name
                LIKE '%".$keyword."%'
                 AND s_category = '".$refine_value."'
  </code></pre>
  
  <p>I need to order the values I select by getting the count of pid in txn table which I get in the query above.</p>
  
  <p>How can I do this?</p>