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;

Answer 1

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