PHP-MySQL - Destroy/Disregard Table Fields if it contains no value/null

Question

Good day! I was trying to LEFT Join one Table into Another. Is it possible to destroy or disregard table fields, for example blotter_entry if it doesn't contain any value or if it is null? By looking at the screenshot below, As you can see I have three tables with blotter_entry . Then it really depends on the user or res_id if the user belongs to the tbl_complainant as tc or tbl_respondent as te, or tbl_victim as tv.

In the screen shot, the query found that the blotter_entry is found in tbl_complainant, How can I disregard the two blotter_entry from tbl_respondent and tbl_victim?

I ask this because when ever I loop this SQL Query and do a echo $row['blotter_entry'] to display it in the website, because if the res_id is detected and found it tbl_respondent and tbl_victim it will not display anything in the webpage. For example res_id is number 3, and he is a respondent/victim. Whenever I output the echo $row['blotter_entry'] in the website. It will not show anything.

Any help would be appreciated. Thank you!

Screenshot after running this SQL Query

SELECT tr.res_id
     , tc.res_id
     , tc.blotter_entry
     , te.res_id
     , te.blotter_entry
     , tv.res_id
     , tv.blotter_entry
     , tb.blotter_id
     , tb.blotter_type
     , tb.blotter_reporteddate
     , tb.blotter_reportedtime
     , tb.blotter_incidentdate
     , tb.blotter_incidenttime
     , tb.blotter_status
     , tc.complainant_name
     , te.respondent_name
     , tv.victim_name
  FROM tbl_resident tr
  LEFT 
  JOIN tbl_complainant tc 
    on tr.res_id = tc.res_id
  LEFT 
  JOIN tbl_respondent te 
    on tr.res_id = te.res_id
  LEFT 
  JOIN tbl_victim tv 
    on tr.res_id = tv.res_id
  LEFT 
  JOIN tbl_blotter tb 
    on tc.blotter_entry = tb.blotter_entry 
    OR te.blotter_entry = tb.blotter_entry 
    OR tv.blotter_entry = tb.blotter_entry
 WHERE tr.res_id = 1;

Did you try to ignore the `NULL` values? read more about [SQL NULL Values](https://www.w3schools.com/sql/sql_null_values.asp) — Burhan Kashour, Nov 04 '20 at 14:40
@BurhanKashour I did. But it doesn't solve the problem, I'm trying to learn MySQL and I've hit a dead end which it this one. — Gamido Valerie, Nov 04 '20 at 14:43
I don't really get what you need, but probably it's COALESCE() function — Your Common Sense, Nov 04 '20 at 14:43
The problem you face is an `ambiguous column name` Which is null in some tables, try to add [aliases](https://www.mysqltutorial.org/mysql-alias/) inside your query to separate between the columns ! — Burhan Kashour, Nov 04 '20 at 14:45
I'm getting the right results that I want. To simplify, I just want to Ignore null results in MySQL JOIN queries. Like if theres a null value. simply remove it or put them all to the right >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> — Gamido Valerie, Nov 04 '20 at 14:54
If you don't want to select any column, why not simply remove it from the list of columns in your query? — Nico Haase, Nov 04 '20 at 16:06
@YourCommonSense it is indeed the COALESCE() function thanks! — Gamido Valerie, Nov 05 '20 at 03:27

score 0 · Accepted Answer · answered Nov 04 '20 at 14:54

You can use coalesce() to use the first non-null value.

select 
    tr.res_id, 
    coalesce(tc.blotter_entry, te.blotter_entry, tv.blotter_entry) as blotter_entry
    tb.blotter_id, 
    tb.blotter_type, 
    tb.blotter_reporteddate, 
    tb.blotter_reportedtime, 
    tb.blotter_incidentdate, 
    tb.blotter_incidenttime, 
    tb.blotter_status, 
    tc.complainant_name, 
    te.respondent_name, 
    tv.victim_name
from tbl_resident tr
left join tbl_complainant tc    on tr.res_id = tc.res_id
left join tbl_respondent te     on tr.res_id = te.res_id
left join tbl_victim tv         on tr.res_id = tv.res_id
left join tbl_blotter tb        on tb.blotter_entry in (tc.blotter_entry, te.blotter_entry, tv.blotter_entry)
where tr.res_id = 1;

Notes:

res_id is also ambiguous in the select clause; it appears multiple times, from different tables. Since that's the column you are using to join and filter, it is sufficient to bring value for the first table in the from clause
I simplified the join condition on the last table to use in rather than repeated or conditions

Ok ok. I can see your point there, lemme try this and study ```coalesce()``` function and the use of the use of ```in``` will get back immediately. — Gamido Valerie, Nov 04 '20 at 14:59
Hello. I studied all night with regards to the functions that can be used in MySQL Queries. It seems that the ```coalesce()``` function is the right answer. Thank you! — Gamido Valerie, Nov 05 '20 at 03:27

PHP-MySQL - Destroy/Disregard Table Fields if it contains no value/null

1 Answers1