how to get all the data from database from 3 tables?

Question

My problem is, i am getting the datas which is present in all the 3 tables.But i want to display the data which is not present in any one of the table(that is empty space or 0 is printed on that item if it's not present in anyone of the table)..i googled and found that it can be done by fullouter join but i don't know how to use it.. Controller Code:

   public function St_statement(){

        $startdate = $this->input->post('SDate');
        $enddate = $this->input->post('EDate');
        $date = str_replace('/', '-', $startdate);
        $newDate = date("Y-m-d", strtotime($date));
        $date2 = str_replace('/', '-', $enddate);
        $newDate2 = date("Y-m-d", strtotime($date2));
        $data['startdate'] = $startdate;
        $data['enddate'] = $enddate;
        if ($this->input->post('all'))
        {
        $this->db->where('billdate >=', $newDate);
        $this->db->where('billdate <=', $newDate2);
        $this->db->where('billdte >=', $newDate);
        $this->db->where('billdte <=', $newDate2);
        $this->db->select("Item");
        $this->db->select("pgroup");
        $this->db->select_sum("Stock");
        $this->db->select_sum("quantity");
        $this->db->select_sum("Qty");
        $this->db->from('opstock');
        $this->db->group_by("Item");
        $this->db->order_by("pgroup",'asc');

        $this->db->join('purchaseitem',' purchaseitem.Prdtname = opstock.Item','OUTER');
         $this->db->join('salesitem','salesitem.Prdtname =  purchaseitem.Prdtname','OUTER');
          $this->db->join('itemmaster','itemmaster.itemname =  purchaseitem.Prdtname','OUTER');
         $this->db->join('pgroup','pgroup.pgroupid = itemmaster.catcode','OUTER');

        $query = $this->db->get();
  $data['query'] = $query;

        $this->load->view('Inventory/St_Stmt', $data);
        //$this->load->view('Inventory/St_Stmt1', $data);
    }
    if($this->input->post('selected'))
    {
        if($name = $this->input->post('businessType'))
        {
        $this->db->where('billdate >=', $newDate);
        $this->db->where('billdate <=', $newDate2);
        $this->db->where('billdte >=', $newDate);
        $this->db->where('billdte <=', $newDate2);
        $this->db->where('pgroup',$name);
        $this->db->select("Item");
        $this->db->select("pgroup");
        $this->db->select_sum("Stock");
        $this->db->select_sum("quantity");
        $this->db->select_sum("Qty");
        $this->db->from('opstock');
        $this->db->group_by("Item");

        $this->db->join('purchaseitem',' purchaseitem.Prdtname = opstock.Item','OUTER');
         $this->db->join('salesitem','salesitem.Prdtname =  purchaseitem.Prdtname','OUTER');
          $this->db->join('itemmaster','itemmaster.itemname =  purchaseitem.Prdtname','OUTER');
         $this->db->join('pgroup','pgroup.pgroupid = itemmaster.catcode','OUTER');

        $query = $this->db->get();
  $data['query'] = $query;

        $this->load->view('Inventory/St_Stmt', $data);


    }

}
}

Hi. You are just asking us to write yet another presentation of outer join, plus a bespoke tutorial. That is too broad a question. And it is a faq because of all the other people who asked without researching or searching. Find a presentation & ask a specific question where you are stuck. Google your question. If you still need to ask then please read & act on [mcve]. Also your text is not clear. Use enough words, sentences & references to parts of an example to clearly express what you want. — philipxy, Nov 15 '18 at 12:34
Possible duplicate of [How to do a FULL OUTER JOIN in MySQL?](https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql) — philipxy, Nov 15 '18 at 12:38

Mohit Rathod · Answer 1 · 2018-11-15T13:06:03.837

$query = "SELECT Item, pgroup, SUM(Stock) AS Stock, SUM(quantity) AS quantity, SUM(Qty) AS Qty FROM opstock LEFT JOIN purchaseitem ON purchaseitem.Prdtname = opstock.Item LEFT JOIN salesitem ON salesitem.Prdtname = purchaseitem.Prdtname LEFT JOIN itemmaster ON itemmaster.itemname = purchaseitem.Prdtname LEFT JOIN pgroup ON pgroup.pgroupid = itemmaster.catcode WHERE billdate >= '".$newDate."' AND billdate <= '".$newDate2."' AND billdte >= '".$newDate."' AND billdte <= '".$newDate2."' GROUP BY Item ORDER BY pgroup ASC UNION SELECT Item, pgroup, SUM(Stock) AS Stock, SUM(quantity) AS quantity, SUM(Qty) AS Qty FROM opstock RIGHT JOIN purchaseitem ON purchaseitem.Prdtname = opstock.Item LEFT JOIN salesitem ON salesitem.Prdtname = purchaseitem.Prdtname LEFT JOIN itemmaster ON itemmaster.itemname = purchaseitem.Prdtname LEFT JOIN pgroup ON pgroup.pgroupid = itemmaster.catcode WHERE billdate >= '".$newDate."' AND billdate <= '".$newDate2."' AND billdte >= '".$newDate."' AND billdte <= '".$newDate2."' GROUP BY Item ORDER BY pgroup ASC SELECT Item, pgroup, SUM(Stock) AS Stock, SUM(quantity) AS quantity, SUM(Qty) AS Qty FROM opstock LEFT JOIN purchaseitem ON purchaseitem.Prdtname = opstock.Item RIGHT JOIN salesitem ON salesitem.Prdtname = purchaseitem.Prdtname LEFT JOIN itemmaster ON itemmaster.itemname = purchaseitem.Prdtname LEFT JOIN pgroup ON pgroup.pgroupid = itemmaster.catcode WHERE billdate >= '".$newDate."' AND billdate <= '".$newDate2."' AND billdte >= '".$newDate."' AND billdte <= '".$newDate2."' GROUP BY Item ORDER BY pgroup ASC UNION SELECT Item, pgroup, SUM(Stock) AS Stock, SUM(quantity) AS quantity, SUM(Qty) AS Qty FROM opstock LEFT JOIN purchaseitem ON purchaseitem.Prdtname = opstock.Item LEFT JOIN salesitem ON salesitem.Prdtname = purchaseitem.Prdtname RIGHT JOIN itemmaster ON itemmaster.itemname = purchaseitem.Prdtname LEFT JOIN pgroup ON pgroup.pgroupid = itemmaster.catcode WHERE billdate >= '".$newDate."' AND billdate <= '".$newDate2."' AND billdte >= '".$newDate."' AND billdte <= '".$newDate2."' GROUP BY Item ORDER BY pgroup ASC";

$query = $this->db->query($query);

Please look at the edited version of your post before you post. Please click on 'edit' & read the edit help & format properly. Please read [ask] & [answer]--this question is too broad & unclear & a faq duplicate & shouldn't be answered. And when answering please don't just dump code. PS There is no full outer join in MySQL. — philipxy, Nov 15 '18 at 12:32
Error Number: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OUTER JOIN purchaseitem ON purchaseitem.Prdtname = opstock.Item FULL OUTER JOIN ' at line 1 — Teddy, Nov 15 '18 at 12:39
SELECT Item, pgroup, SUM(Stock) AS Stock, SUM(quantity) AS quantity, SUM(Qty) AS Qty FROM opstock FULL OUTER JOIN purchaseitem ON purchaseitem.Prdtname = opstock.Item FULL OUTER JOIN salesitem ON salesitem.Prdtname = purchaseitem.Prdtname FULL OUTER JOIN itemmaster ON itemmaster.itemname = purchaseitem.Prdtname FULL OUTER JOIN pgroup ON pgroup.pgroupid = itemmaster.catcode WHERE billdate >= '1' AND billdate <= '1' AND billdte >= '2018-11-15' AND billdte <= '2018-11-15' GROUP BY Item ORDER BY pgroup ASC Filename: D:/xampp/htdocs/Yuva/system/database/DB_driver.php Line Number: 691 — Teddy, Nov 15 '18 at 12:39
@esakkichandra MySQL does not have full outer join syntax. See my comments on this answer & the question. — philipxy, Nov 15 '18 at 12:48
@MacRathod it shows an error Incorrect usage of UNION and ORDER BY — Teddy, Nov 15 '18 at 13:15

how to get all the data from database from 3 tables?

1 Answers1