CODEWITHSUNDEEP
phpmysql
Hard Spocker
Hard Spocker

Reputation: 765

How to compare tables with INNER JOIN and return true?

I have here 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 do i get both tables and compare if the device.activation_id == activation.id i want to list these devices separately, see example:

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

How can I have a very execution efficient solution using inner joint in this 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;
        }
    }
}

Upvotes: 4

Views: 283

Answers (4)

dncook
dncook

Reputation: 341

You can do this with two separate SQL queries, one to get the active devices, and one to get the inactive devices, like so.

Active:

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

Inactive:

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

Alternately, if you want to do this all in one query, you could use the following query, and get a series of rows with each ID and one boolean value, indicating whether the other table matches or not.

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

Edit: If you want to also get the activation code, you can use the following query. For devices without an activation code, that column will be NULL in the result set.

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

Upvotes: 2

randiel
randiel

Reputation: 290

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 view the results: http://sqlfiddle.com/#!9/79abb6/13

Upvotes: 2

vishal dhanopia
vishal dhanopia

Reputation: 19

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

Upvotes: 0

akm
akm

Reputation: 830

Most effective way to perform this operation at MySQL level; since DB systems are very effective working on its data in comparison to script that pull data and work on it.

It would be very cumbersome to do it in one go.

You might want to do something like this.

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

This will fetch you the list of all active devices and in second query you can filter rest of the inactive devices once you have the active ones.

Upvotes: 1

Related Questions