title: MySQL Order By in Right Join Clause
body: <p>I'm trying to do a right join in MySQL like so:</p>
  
  <pre><code>SELECT customers.id,customers.firstname,customers.lastname,customers.email,orders.time,orders.notes,pendings.date_updated,pendings.issue,appointments.closed,appointments.job_description,backup_plans.expiration FROM customers
  RIGHT JOIN orders
  ON customers.id = orders.customer_id
  ORDER BY orders.time DESC LIMIT 1
  RIGHT JOIN pendings
  ON customers.id = pendings.customer_id
  ORDER BY pendings.date_updated DESC LIMIT 1
  RIGHT JOIN appointments
  ON customers.id = appointments.customer_id
  ORDER BY appointments.closed DESC LIMIT 1
  RIGHT JOIN backup_plans
  ON customers.id = backup_plans.customer_id
  ORDER BY backup_plans.expiration DESC LIMIT 1
  </code></pre>
  
  <p>My intent is this: to select customers' name and email, along with the most recent order, pending, appointment, and backup plan exploration. When I execute this I get a syntax error:</p>
  
  <pre><code>#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RIGHT JOIN pendings
  ON customers.id = pendings.customer_id
  ORDER BY pendings.d' at line 5
  </code></pre>
  
  <p>I'm unfamiliar with joins and would appreciate any help.</p>
  
  <hr />
  
  <p><strong>EDIT 1:</strong></p>
  
  <p>It seems that I need to make a subquery per DanK's suggestion like so: </p>
  
  <pre><code>SELECT customers.id,customers.firstname,customers.lastname,customers.email,orderstmp.time,orderstmp.notes FROM customers
  RIGHT JOIN (
      SELECT orders.time,orders.notes,orders.customer_id FROM orders ORDER BY orders.time DESC LIMIT 1
  ) as orderstmp ON orderstmp.customer_id = customers.id
  </code></pre>
  
  <p>But when I do this, I only get one row result, whereas I want <strong>all</strong> the customer information.</p>
  
  <hr />
  
  <p><strong>EDIT 2:</strong></p>
  
  <p>Per Tom H's suggestion, I've built this query:</p>
  
  <pre><code>SELECT
      customers.id,
      SQ_O.time,
      SQ_O.notes
  FROM customers
  LEFT JOIN (
      SELECT
          customers.id,
          orders.time,
          orders.notes
      FROM customers
      LEFT JOIN orders ON orders.customer_id = customers.id
      ORDER BY orders.time DESC LIMIT 1
  ) AS SQ_O ON SQ_O.id = customers.id
  </code></pre>
  
  <p>which has all blank time and notes fields</p>
  
  <p>and</p>
  
  <pre><code>SELECT
      customers.id,
      O1.time,
      O1.notes
  FROM customers
  LEFT JOIN orders AS O1 ON O1.customer_id = O1.id
  LEFT JOIN orders AS O2 ON O2.customer_id = customers.id AND O2.time &gt; O1.time WHERE O2.customer_id IS NULL
  </code></pre>
  
  <p>Which reaches max execution time. I'm guessing this is due to my lack of familiarity with what's possible in MySQL in comparison to other dialects.</p>
  
  <p>I also tried <strong>Correlated subqueries</strong> like this:</p>
  
  <pre><code>SELECT
      customers.firstname,
      customers.lastname,
      customers.email,
      (
          SELECT CONCAT(orders.time,': ',orders.notes)
          FROM orders
          WHERE orders.customer_id = customers.id
          ORDER BY orders.time DESC LIMIT 1
      ) as last_order
  FROM customers
  </code></pre>
  
  <p>But the "last_order" column comes up blank.</p>
  
  <hr />
  
  <p><strong>FINAL, DISAPPOINTING EDIT</strong></p>
  
  <p>After trying a number of really stellar suggestions that helped me learn SQL significantly, I decided to write a PHP script to get me what I want. The project's under a bit of a deadline so whatever works, works. Thanks everyone!</p>