StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSelect value in mysql but check another database at clause WHERE
text
Body
copied!<p>I'm trying to select values from a database, but I need to check another value in another database .</p> <p>I created this code, but only get 1 result and I don't know why:</p> <pre><code>SELECT `id` FROM `mc_region` WHERE `is_subregion` = 'false' AND lastseen < CURDATE() - INTERVAL 20 DAY AND (SELECT id_region FROM mc_region_flags WHERE flag <> 'expire' AND id_region = mc_region.id ) LIMIT 0, 30 </code></pre> <p>What I've made wrong?</p> <p>@Edit<br> I think I know why this code is not working. At database mc_region_flags not all records from the primary database has flag. </p> <p>I would like to do the following:</p> <p>1º Select all records on the first database, where is not subregion and lastseen is more than 20 day<br> 2º Check if any result on the 1st database has flag 'expire', if yes, they are not included in the result.</p> <p>I cant do this in 1 only SQL Code?</p> <p>@Edit2 </p> <p>I created this code that simulate <code>FULL JOIN</code> but seems that <code>WHERE</code> is not work</p> <pre><code>SELECT * FROM mc_region AS r RIGHT OUTER JOIN mc_region_flags AS f ON r.id = f.id_region UNION ALL SELECT * from mc_region AS r LEFT OUTER JOIN mc_region_flags AS f ON r.id = f.id_region WHERE r.is_subregion = 'false' AND f.flag = 'exipre' AND r.lastseen < CURDATE() - INTERVAL 20 DAY </code></pre> <p>Problems WHERE not work</p> <ol> <li><code>f.flag</code> is not 'expire' </li> <li><code>f.lastseen</code> is not > 20 days</li> </ol> <p><img src="https://i.stack.imgur.com/atj2Y.png" alt="results"></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload