Jarla Jarla - 2 months ago 12
MySQL Question

How can I mark the values from one table, that are not in another table (mySQL)?

In mySQL this is my table

animals
:

+-----------+--------+--------+
| animal_ID | name | animal |
+-----------+--------+--------+
| 1 | alan | dog |
| 2 | sam | frog |
| 3 | marion | cat |
| 4 | george | rabbit |
| 5 | bob | bird |
+-----------+--------+--------+


and this is my table
orders


+----------+-----------+
| date | animal_ID |
+----------+-----------+
| 02.03.16 | 4 |
| 12.04.16 | 3 |
| 18.07.16 | 1 |
+----------+-----------+


I want to list all animals but mark the animals that are NOT in the orders table red. This is my expected result



<table >
<tr>
<td>alan</td>
</tr>
<tr>
<td style="color:red">sam</td>
</tr>
<tr>
<td>marion</td>
</tr>
<tr>
<td>george</td>
</tr>
<tr>
<td style="color:red">bob</td>
</tr>
</table>





I have now the problem, that I do not know how to list all animals from the animal table, I just get the animals in my list that are not in the orders table:

$sql = 'SELECT * FROM animals a WHERE EXISTS (SELECT * FROM orders o WHERE o.animal_ID = a.animal_ID)';


Here my result is this:



<table >
<tr>
<td>alan</td>
</tr>
<tr>
<td>marion</td>
</tr>
<tr>
<td>george</td>
</tr>
</table>




Answer

You need to make this script using two query.

 1. Query for all the animals.
 2. Query with the animals ID in the orders.

The first one is:

$sql = 'SELECT  *  FROM animals";

Loop this and check in the order table inside the first loop-

$sql2 = "SELECT * FROM orders WHERE  animal_ID = fetched_animal_ID";
if(mysqli_num_rows($sql2) > 0){
    // print the normalrows here
}else{
    // print the red rows here
}

==============================================================

You can do this using the LEFT JOIN as well-

$sql = "SELECT *, o.animal_ID as o_animal FROM animals a LEFT JOIN orders o 
        ON a.animal_ID = o.animal_ID";

Online Example

Now you need to check the o_animal if it is null or empty then use the red rows and else the normal.

If you have any question please ask me.

Comments