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.
Advertisement
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)