Bailey Bailey - 4 months ago 9
MySQL Question

MySQL conditions between two tables

Table "servers":

+----+----------------+
| id | disabled_until |
+----+----------------+
| 1 | 0 | // disabled_until is an epoch timestamp
| 2 | 146893210 | // (irrelevant)
| 3 | 1468969442 |
+----+----------------+


Table "server_disables":

+----+-----------+-------+----------------+
| id | server_id | tld | disabled_until |
+----+-----------+-------+----------------+
| 1 | 1 | .com | 0 |
| 2 | 1 | .org | 1468969900 |
| 2 | 3 | .com | 1468969900 |
+----+-----------+-------+----------------+




I am trying to make a query which selects rows from servers where:


  1. There's 0 rows in server_disables where server_disables.server_id = servers.id or

  2. There's 1+ rows in server_disables where server_disables.server_id = servers.id and the server_disables.disabled_until is less than a certain time (say, x)



I believe I've created a query which can accomplish #2, but not #1. I don't know where to continue or how to add a case for when there's 0 matching rows in server_disables.
You can see my attempt in
OR d.disabled_until IS NULL
but it isn't working.

Query:

SELECT s.* FROM servers s
INNER JOIN server_disables d ON (d.server_id = s.id AND d.tld = '.com')
WHERE (s.disabled_until < 1468984373)
AND (d.disabled_until < 1468984373 OR d.disabled_until IS NULL)
ORDER BY RAND() LIMIT 5


My question is how to make my query also show rows from the "servers" table where there's 0 rows merged from the
INNER JOIN
.

Answer

You should use LEFT JOIN, and maybe there are duplicated rows, so you may also need to use DISTINCT, try this:

SELECT DISTINCT s.* FROM servers s 
LEFT JOIN server_disables d ON (d.server_id = s.id AND d.tld = '.com') 
WHERE (s.disabled_until < 1468984373) 
AND (d.disabled_until IS NULL OR d.disabled_until < 1468984373)
ORDER BY RAND() LIMIT 5
Comments