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












-1















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





    }



}

}





php mysql codeigniter full-outer-join







share|improve this question




















  • 1





    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 Minimal, Complete, and Verifiable example. 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








  • 1





    Possible duplicate of How to do a FULL OUTER JOIN in MySQL?

    – philipxy
    Nov 15 '18 at 12:38
















-1















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





    }



}

}





php mysql codeigniter full-outer-join







share|improve this question




















  • 1





    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 Minimal, Complete, and Verifiable example. 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








  • 1





    Possible duplicate of How to do a FULL OUTER JOIN in MySQL?

    – philipxy
    Nov 15 '18 at 12:38














-1












-1








-1








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





    }



}

}





php mysql codeigniter full-outer-join







share|improve this 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);





    }



}

}





php mysql codeigniter full-outer-join




php mysql codeigniter full-outer-join






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 15 '18 at 12:45









philipxy

11.7k42253




11.7k42253










asked Nov 15 '18 at 12:14









TeddyTeddy

6110




6110








  • 1





    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 Minimal, Complete, and Verifiable example. 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








  • 1





    Possible duplicate of How to do a FULL OUTER JOIN in MySQL?

    – philipxy
    Nov 15 '18 at 12:38














  • 1





    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 Minimal, Complete, and Verifiable example. 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








  • 1





    Possible duplicate of How to do a FULL OUTER JOIN in MySQL?

    – philipxy
    Nov 15 '18 at 12:38








1




1





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 Minimal, Complete, and Verifiable example. 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







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 Minimal, Complete, and Verifiable example. 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






1




1





Possible duplicate of How to do a FULL OUTER JOIN in MySQL?

– philipxy
Nov 15 '18 at 12:38





Possible duplicate of How to do a FULL OUTER JOIN in MySQL?

– philipxy
Nov 15 '18 at 12:38












1 Answer
1






active

oldest

votes


















1














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






share|improve this answer


























  • Please look at the edited version of your post before you post. Please click on 'edit' & read the edit help & format properly. Please read How to Ask & How to 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













  • it shows an error

    – Teddy
    Nov 15 '18 at 12:38













  • 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











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53319290%2fhow-to-get-all-the-data-from-database-from-3-tables%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









1














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






share|improve this answer


























  • Please look at the edited version of your post before you post. Please click on 'edit' & read the edit help & format properly. Please read How to Ask & How to 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













  • it shows an error

    – Teddy
    Nov 15 '18 at 12:38













  • 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
















1














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






share|improve this answer


























  • Please look at the edited version of your post before you post. Please click on 'edit' & read the edit help & format properly. Please read How to Ask & How to 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













  • it shows an error

    – Teddy
    Nov 15 '18 at 12:38













  • 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














1












1








1







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






share|improve this answer















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







share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 15 '18 at 13:06

























answered Nov 15 '18 at 12:30









Mac RathodMac Rathod

215




215













  • Please look at the edited version of your post before you post. Please click on 'edit' & read the edit help & format properly. Please read How to Ask & How to 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













  • it shows an error

    – Teddy
    Nov 15 '18 at 12:38













  • 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



















  • Please look at the edited version of your post before you post. Please click on 'edit' & read the edit help & format properly. Please read How to Ask & How to 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













  • it shows an error

    – Teddy
    Nov 15 '18 at 12:38













  • 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

















Please look at the edited version of your post before you post. Please click on 'edit' & read the edit help & format properly. Please read How to Ask & How to 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







Please look at the edited version of your post before you post. Please click on 'edit' & read the edit help & format properly. Please read How to Ask & How to 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















it shows an error

– Teddy
Nov 15 '18 at 12:38







it shows an error

– Teddy
Nov 15 '18 at 12:38















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





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





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





@esakkichandra MySQL does not have full outer join syntax. See my comments on this answer & the question.

– philipxy
Nov 15 '18 at 12:48




















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid …



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53319290%2fhow-to-get-all-the-data-from-database-from-3-tables%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







-

Popular posts from this blog

The Sandy Post

Image
The Sandy Post From Wikipedia, the free encyclopedia Jump to navigation Jump to search The Sandy Post Type Weekly Newspaper Format Tabloid Owner(s) Community Newspapers/Pamplin Media Group Publisher J. Mark Garber Editor Steve Brown Founded 1937  ( 1937 ) Headquarters Sandy, Oregon Circulation 3,800 Website www.pamplinmedia.com/sandy-post-home/ This article needs additional citations for verification . Please help improve this article by adding citations to reliable sources. Unsourced material may be challenged and removed. (February 2010) (Learn how and when to remove this template message) The Sandy Post is a weekly newspaper, published in Sandy, Oregon, United States. The paper, founded in 1937, serves the communities of Sandy, Boring, the Villages at Mount Hood and the surrounding areas. The newspaper is owned by Community Newspapers/Pamplin Media Group, a company of ...
Read more

Danny Elfman

Image
Danny Elfman From Wikipedia, the free encyclopedia Jump to navigation Jump to search Danny Elfman Elfman at the 2010 San Diego Comic-Con Born Daniel Robert Elfman ( 1953-05-29 ) May 29, 1953 (age 65) Los Angeles, California, U.S. Spouse(s) Bridget Fonda ( m.  2003) Children 1 Musical career Genres Rock [1] ska [2] new wave film music video game music Occupation(s) Composer, singer, songwriter, record producer Instruments Trombone guitar percussion vocals keyboards [3] Years active 1972–present Associated acts Oingo Boingo James Newton Howard Daniel Robert Elfman (born May 29, 1953) is an American composer, singer, songwriter, and record producer. Elfman first became known for being the lead singer and songwriter for the band Oingo Boingo from 1974 to 1995. He is well known for scoring films and television shows, particularly his frequent collabora...
Read more

Pages that link to "Head v. Amoskeag Manufacturing Co."

Image
Help Pages that link to "Head v. Amoskeag Manufacturing Co." ← Head v. Amoskeag Manufacturing Co. Jump to navigation Jump to search What links here Page:   Namespace:  all (Article) Talk User User talk Wikipedia Wikipedia talk File File talk MediaWiki MediaWiki talk Template Template talk Help Help talk Category Category talk Portal Portal talk Book Book talk Draft Draft talk TimedText TimedText talk Module Module talk Gadget Gadget talk Gadget definition Gadget definition talk   Invert selection Filters Hide transclusions | Hide links | Hide redirects The following pages link to Head v. Amoskeag Manufacturing Co. External tools: Show redirects only View (previous 50 | next 50) (20 | 50 | 100 | 250 | 500) Amoskeag Manufacturing Company ‎ (links | edit) List of United States Supreme Court cases by the Waite Court ‎ (links | edit) Talk:Head v. Amoskeag Manuf...
Read more