php - 如何从 3 个表中获取数据库中的所有数据？

Question

我的问题是，我正在获取所有 3 个表中都存在的数据。但是我想显示任何一个表中都不存在的数据（如果不是，则为空白空间或在该项目上打印 0出现在表的任何人中）..我用谷歌搜索并发现它可以通过fullouter join来完成，但我不知道如何使用它..控制器代码：

   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);


    }

}
}

标签： phpmysqlcodeigniterfull-outer-join

Answer 1

$query = "SELECT Item, pgroup, SUM( Stock) AS Stock, SUM( quantity) AS quantity, SUM( Qty) AS QtyFROM opstock LEFT JOIN purchaseitemON purchaseitem. Prdtname= opstock. Item LEFT JOIN salesitemON salesitem. Prdtname= purchaseitem. Prdtname LEFT JOIN itemmasterON itemmaster. itemname= purchaseitem. Prdtname LEFT JOIN pgroupON pgroup. pgroupid= itemmaster. catcodeWHERE billdate>= ' ".$newDate."' AND billdate<= '".$newDate2."' AND billdte>= '".$newDate."' AND billdte<= '".$newDate2."' GROUP BY ItemORDER BY pgroupASC UNION SELECT Item, pgroup, SUM ( Stock) 作为Stock,总和（ quantity）作为quantity，总和（Qty)Qty从右opstock 连接purchaseitem开始purchaseitem。Prdtname= opstock。Item 左salesitem加入salesitem。Prdtname= purchaseitem。Prdtname 左itemmaster加入itemmaster。itemname= purchaseitem。Prdtname 左pgroup加入pgroup。pgroupid= itemmaster。catcodeWHERE billdate>= '".$newDate."' AND billdate<= '".$newDate2."' AND billdte>= '".$newDate."' AND billdte<= '".$newDate2."' GROUP BY ItemORDER BY pgroupASC SELECT Item, pgroup, SUM( Stock) AS Stock, SUM( ) quantityAS quantity, SUM( ) QtyASQty从左连接开始。= 。opstockpurchaseitempurchaseitemPrdtnameopstockItemsalesitem开salesitem。Prdtname= purchaseitem。Prdtname 左itemmaster加入itemmaster。itemname= purchaseitem。Prdtname 左pgroup加入pgroup。pgroupid= itemmaster。catcodeWHERE billdate>= '".$newDate."' AND billdate<= '".$newDate2."' AND billdte>= '".$newDate."' AND billdte<= '".$newDate2."' GROUP BY ItemORDER BY pgroupASC UNION SELECT Item, pgroup, SUM( Stock) AS Stock, SUM( quantity) AS quantity, SUM( Qty) ASQty从opstock LEFT JOIN purchaseitemON开始purchaseitem。Prdtname= opstock。Item 左salesitem加入salesitem。Prdtname= purchaseitem。正确Prdtname 加入。itemmasteritemmasteritemnamepurchaseitem. Prdtname 左pgroup加入pgroup。pgroupid= itemmaster。catcodeWHERE billdate>= '".$newDate."' AND billdate<= '".$newDate2."' AND billdte>= '".$newDate."' AND billdte<= '".$newDate2."' GROUP BY ItemORDER BY pgroupASC" ;

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