MySQL query result does not return some rows that should be in the result

This is a query that should return a match result of a lost item that has been found. According to my database below, I expect it to return two rows, but only one row is returned. Before I added the second rows in tbl_lost_items and tbl_found_items the result was an empty set.

SELECT L.user_id,L.item_type_id,F.found_by FROM tbl_users AS U 
      JOIN tbl_lost_items AS L ON U.id = L.user_id  
      JOIN tbl_item_types AS IT ON IT.id = L.item_type_id
      JOIN tbl_found_items AS F ON F.item_type_id = IT.id
   WHERE U.first_name = F.first_name_in_id AND U.last_name = F.last_name_in_id;

This is the result

+---------+--------------+----------+
| user_id | item_type_id | found_by |
+---------+--------------+----------+
|       6 |            3 |        4 |
+---------+--------------+----------+
1 row in set (0.01 sec)

These are my tables

tbl_users

+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | mohamed    | diaby     |
|  2 | estern     | winluck   |
|  3 | adam       | kisombe   |
|  4 | william    | patrick   |
|  5 | aisha      | diaby     |
|  6 | hanna      | shange    |
+----+------------+-----------+

tbl_found_items

+------------------+-----------------+--------------+----------+
| first_name_in_id | last_name_in_id | item_type_id | found_by | 
+------------------+-----------------+--------------+----------+
| mohamed          | diaby           |            1 |        2 |
| hanna            | shange          |            3 |        4 |
+------------------+-----------------+--------------+----------+

tbl_item_types

+----+-----------------------+
| id | item_type             |
+----+-----------------------+
|  1 | Citizen Identity Card |
|  2 | NHIF                  |
|  3 | Passport              |
|  4 | Voters Card           |
+----+-----------------------+

tbl_lost_items

+----+---------+--------------+---------------------+-----------+-----------+-------------+
| id | user_id | item_type_id | timestamp           | status    | region_id | district_id |
+----+---------+--------------+---------------------+-----------+-----------+-------------+
|  1 |       1 |            1 | 2020-01-26 18:58:04 | Not Found |         6 |          30 |
|  2 |       6 |            3 | 2020-01-27 14:12:19 | Not Found |        11 |          62 |
+----+---------+--------------+---------------------+-----------+-----------+-------------+

Also, when I delete a row in tbl_lost_items and tbl_found_items that should return a match, but does not appear in the query result, and then re-enter the same information again in those two tables the result includes a match, but then if there is a new record inserted in tables tbl_lost_items and tbl_found_items that should result in a match the match does not appear in the query result. I can’t figure out what the problem is here. Is this a bug in MySQL? I’m using MySQL 8.0.16 on a mac.

Answer

join don’t match .. in this case use left join

could be some of your values don’t match be sure you have not hiddden chars using TRIM()

 SELECT L.user_id,L.item_type_id,F.found_by FROM tbl_users AS U 
      JOIN tbl_lost_items AS L ON U.id = L.user_id  
      JOIN tbl_item_types AS IT ON IT.id = L.item_type_id
      JOIN tbl_found_items AS F ON F.item_type_id = IT.id
      WHERE  TRIM(U.first_name)  = TRIM(F.first_name_in_id)
      AND TRIM(U.last_name) = TRIM(F.last_name_in_id)

Advertisement

Answer