Multiple SELECT with LEFT JOIN to order results

Question

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 <> 2 return NULL
  • IF open <> 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)

Answer 1

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.

Answer 2

I don't see why you need subquery t3 - I think it can be written as (untested):

-- get id, name and open only once
SELECT DISTINCT t1.id , t1.name , table2.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 table2
ON t1.id = table2.name_id
WHERE open=1 AND type=2

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

I don't think it's possible to avoid the t1 subquery, as we need to sort by date there, but by name for the final result.