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)

• BCdotWEB: Try to write a title that summarizes what your code does, not what you want to get out of a review. Please see How to get the best value out of Code Review - Asking Questions for guidance on writing good question titles.
• Michel: @BCdotWEB. If you have any suggestions, please? I've been thinking about a good title for quite a while, as I suspected this one was a bit too cryptic, but couldn't come up with something that made any sense. But I will try to formulate something else.
• montelof: I didn't get how "ELSE return 1" is meet here. besides that I think the query is correct.
• Toby Speight: @montelof, it seems to come from SELECT open ... WHERE open=1. If it's not obvious, then that's something to pick up at review.

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.

• Michel: Thanks, this works and indeed is a bit quicker. As for your second point, call it laziness but table 2 is called upon about 20 times more than table 1, and as I have the values at hand when inserting a record, it's just so I do not have to join with table 1 all the time to get the name. Maybe bad practice, but it makes life a bit more simple :-)

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.

• Michel: Thanks for your answer, but this isn't working. The query returns only one name, where 3 is correct in my example.