Multiple SELECT with LEFT JOIN to order results I have two tables, looking like this:

Table 1

id | name |          date
---+------+---------------------
 1 | zaak |  2016-08-03 11:37:40  -- top 1 dates, name #5
 2 | aap  |  2016-08-01 11:40:35  
 3 | corn |  2016-08-02 11:42:16  -- top 3 dates, name #3
 4 | quar |  2016-08-02 11:42:28  -- top 2 dates, name #4
 5 | alfa |  2016-07-04 11:44:29  


Table 2

id | open | name  | name_id | type
---+------+-------+---------+-----
 1 |   1  | aap   |    2    |   2  -- type = 2, open = 1 : 1
 2 |   1  | quar  |    4    |   2  -- type = 2, open = 1 : 1
 3 |   1  | quar  |    4    |   2  -- type = 2, open = 1 : 1
 4 |   1  | dinky |    1    |   1  -- type = 1, open = 1 : NULL
 5 |   2  | quar  |    1    |   1  -- type = 1, open = 2 : NULL


The result I need is id and name from table 1 and open from table 2.

The requirements:


from table 1: select the newest three by date
order those three by name from A - Z
from table 2: select open   


IF name not there return NULL   
IF type &lt;> 2  return NULL   
IF open &lt;> 1  return NULL   
ELSE              return 1



With a lot of trial and error I constructed this query:

-- get id, name and open only once
SELECT DISTINCT t1.id , t1.name , t3.open 

-- select names from table 1, order them by date and return first 3
FROM (SELECT id , name FROM table1 ORDER BY date DESC LIMIT 3) AS t1

-- get open from table 2 IF id is there, open = 1 and type =2
LEFT JOIN ( ( SELECT open , name_id  
              FROM table2 
              WHERE open=1 AND type=2
            ) AS t3
          ) 
ON t1.id = t3.name_id 

-- order by name from A-Z
ORDER BY t1.name ASC


The result I get is correct:

id | name | open
---+------+-----
 3 | corn | NULL  (correct)
 4 | quar | 1     (correct)
 1 | zaak | NULL  (correct)


As my knowledge of MYSQL is limited, I wonder


would this query always return the desired result? (the data in the tables is limited at the moment and I have to change values by hand to see different results, so testing all variations proves a bit difficult) 
is there anything I could improve on this query, or should I construct an entirely different query? (as triple SELECT to get the desired result seems a bit overdone)
First of all you don't need so many subqueries. all of your code can be done in given way 

SELECT DISTINCT t1.id, t1.name, t2.open
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.name_id AND t2.open = 1 AND type = 2
ORDER BY t1.date DESC, t1.name ASC LIMIT 3


Works exactly the way you want it, plus is almost 3 times faster on my machine. (0.0012 seconds vs 0.0034)

for additional reading on subqueries and joins https://stackoverflow.com/questions/3856164/sql-joins-vs-sql-subqueries-performance
https://stackoverflow.com/questions/2577174/join-vs-sub-query

Second point is that I think you should not be storing name value in table2. i don't know your project requirements or specifics but one of the major point of relational databases is to diversify data and avoid copies of the same data in different places. 

table2 is connected with table1 with name_id which means it indirectly provides name value and i would suggest removing that column from table2 if it's not absolutely neccessery for your project, or it would mean u should rewrite  big portion of your code.