FESP Inter FESP Inter - 4 months ago 8
SQL Question

get notification from single table and join with multiple tables and result as single array

i have a table like enter image description here

and select all notifications from this table and join with below mentioned tables


  1. tbl_notice
    enter image description here

  2. tbl_task



enter image description here


  1. tbl_assignment



enter image description here

and i am trying to fetch datas from tables like

$sql = "select * from(
(
SELECT a.refer_id as id ,a.refer_tbl,b.task_name as name FROM tbl_notification as a
LEFT JOIN tbl_task as b ON (a.refer_id= b.task_id )ORDER BY a.created_date desc
) UNION
(
SELECT c.refer_id as id ,c.refer_tbl, d.notice_title as name FROM tbl_notification as c
LEFT JOIN tbl_notice as d ON (c.refer_id = d.notice_id)ORDER BY c.created_date desc
) UNION
(
SELECT e.refer_id as id,e.refer_tbl, f.title as name FROM tbl_notification as e
LEFT JOIN tbl_assignment as f ON (e.refer_id = f.assignment_id) ORDER BY e.created_date desc
))";
$query= $this->db->query($sql)->get();


the result shows the data from first table where join (in case tbl_task) other tbl_notice and tbl_assignment details are null result demo like this
enter image description here

Please help me to correct this.....

Answer

You're performing a LEFT (OUTER) JOIN when it sounds like you're needing a plain old (INNER) JOIN. Try this:

SELECT a.refer_id as id ,a.refer_tbl,b.task_name as name FROM tbl_notification as a
JOIN tbl_task as b ON (a.refer_id= b.task_id )ORDER BY a.created_date desc
UNION
SELECT c.refer_id as id ,c.refer_tbl, d.notice_title as name FROM tbl_notification as c
JOIN tbl_notice as d ON (c.refer_id = d.notice_id)ORDER BY c.created_date desc
UNION
SELECT e.refer_id as id,e.refer_tbl, f.title as name FROM tbl_notification as e
JOIN tbl_assignment as f ON (e.refer_id = f.assignment_id) ORDER BY e.created_date desc