How to compare tables with INNER JOIN and return true?

Question

I have a table:

activation
+--------+----------------+
|   id   | activation_code|
+--------+----------------+
|   0001 | 00111          |
|   0002 | 00110          |
|   0003 | 00100          |
+--------+----------------+

and another table:

device
+--------+--------------+
|   id   |activation_id |
+--------+--------------+
|   12121| 0001         |
|   12122| 0002         |
|   12123| 0003         |
|   12124| 0004         |
|   12125| 0005         |
+--------+--------------+

how can I get both tables and compare if device.activation_id == activation.id I want to list these devices separately, see an example:

devices
+--------+----------+
| active |inactive  |
+--------+----------+
|   12121| 12124    |
|   12122| 12125    |
|   12123|          |
|        |          |
|        |          |
+--------+----------+

How can I get an effective solution using internal joint when using PHP?

I have a solution here, but its not very nice:

foreach ($device_query as $res_d) {
    foreach ($activation_query as $res_a) {
        if ($res_d->activation_id == $res_a->id) {
            $is_active = true;
            break;
        }
        else {
            $is_active = false;
        }
    }
}

Answer 1

You can do this in one SQL Select to get both devices:

select 
    if(activation_code is not null, d.id, null) as active,
    if(activation_code is null, d.id, null) as inactive,
    activation_code
from device d
left join activation a
    on a.id = d.activation_id

If you want to view the results: http://sqlfiddle.com/#!9/79abb6/13

Answer 2

SQL-, , , .

:

SELECT device.id AS active FROM device INNER JOIN activation ON device.activation_id=activation.id

:

SELECT device.id AS inactive FROM device WHERE device.activation_id NOT IN (SELECT id FROM activation)

, , , , .

SELECT device.id, device.activation_id IN (SELECT id FROM activation) AS activated FROM device

: , . NULL .

SELECT device.id, activation.activation_code FROM device LEFT OUTER JOIN activation ON device.activation_id=activation.id;

Answer 3

MySQL; script, .

.

, - .

SELECT d.id from FROM activation as a, device as d WHERE a.id = d.activation_id; 

This will display a list of all active devices, and in the second query, you can filter the rest of the inactive devices when you have active ones.

Answer 4

select * 
from db2 d2
left join db1 d1 on d2.id=d1.id