Problem in joining three tables

Databases
1

Hi…

I encountered problem in my query to display data.
here is my separate query then i mix up:


SELECT kc.PCODE, kc.count_wip_chemical_weighing, kc.count_wip_compounding, kc.count_wip_extrusion, kc.count_wip_forming, kc.count_wip_deflashing, kc.kanban, kc.virtual, p.max_lot, p.min_lot FROM kanban_checker kc JOIN plan p ON kc.PCODE = p.PCODE  ORDER BY p.PCODE;



SELECT  kd.LOT_CODE, kd.wip_chemicalweighing, kd.wip_compounding, kd.wip_extrusion, kd.wip_forming, kd.wip_deflashing
FROM kanban_data kd JOIN plan p ON kd.PCODE = p.PCODE  ORDER BY p.PCODE;

mix query


SELECT p.PCODE, kd.LOT_CODE, kd.wip_chemicalweighing, kd.wip_compounding, kd.wip_extrusion, kd.wip_forming, kd.wip_deflashing, kc.count_wip_chemical_weighing, kc.count_wip_compounding, kc.count_wip_extrusion, kc.count_wip_forming, kc.count_wip_deflashing, kc.kanban, kc.virtual, p.max_lot, p.min_lot FROM kanban_data kd JOIN kanban_checker kc ON kd.PCODE = kc.PCODE JOIN plan p ON kc.PCODE = p.PCODE  ORDER BY p.PCODE;

I want to display PCODE once. like one P35, one P35M .I want it to display with the kc.count_wip_chemical_weighing, kc.count_wip_compounding, kc.count_wip_extrusion, kc.count_wip_forming, kc.count_wip_deflashing, kc.kanban, kc.virtual, p.max_lot, p.min_lot,…No PCODE and max_lot, virtual, min_lot, kanban in this fields:
kd.LOT_CODE, kd.wip_chemicalweighing, kd.wip_compounding, kd.wip_extrusion, kd.wip_forming, kd.wip_deflashing

I attach the sample format I need to display which is the excel type and the html type the result from query.

Thank you

2

Not sure what is wrong with your query…
but in general I suggest you skip the joins and make the “joining” with php

either mapping or just fill the array in a loop
especially if the data set is rather large, it will be much faster that way and it will have a smaller footprint…

the join syntax is quite well explained in many tuts - google for it…
however - consider other alternatives first…

regards
Hensel

3

Can you give me an example.

Thank you

4

I tried this:


<?php
  error_reporting(0);
$con = mysql_connect('localhost', 'root','');

if (!$con) {
    echo 'failed';
    die();
}

mysql_select_db("mes", $con);
?>
<style type="text/css">
table {
    margin: 9px;
}

th {
    font-family: Arial, Helvetica, sans-serif;
    font-size: .7em;
    background: #694;
    color: #FFF;
    padding: 2px 6px;
    border-collapse: separate;
    border: 1px solid #000;
}

td {
    font-family: Arial, Helvetica, sans-serif;
    font-size: .7em;
    border: 1px solid #DDD;
    text-align: right;
}
</style>
<script type="text/javascript">
function showDetails(pcode) {

 var clickElement = pcode.value;
 var click_id = pcode.id;

// var value_ =  document.getElementById(click_id).checked
 //    =  document.getElementById(clickElement).checked;
 //var Table = (document.getElementsByName('list')[0].value);

 var Table = document.getElementById('kanban_list');

 var rows = Table.rows;

 var strSelect = document.getElementById(click_id).value;
 //alert(strSelect)

 for (var i = 0; i < rows.length; i++) {
      var row = rows[i];
    //row.style.display = (row.id.substr(0,3) == strSelect) ?      'none'         :          '';
    //row.style.display = (row.id.substr(0,3) == strSelect) ?
   // row.style.display = 'none';
   // row.style.display = '';

    if (row.id.substr(0,3) == strSelect) {
        row.style.display = ((document.getElementById(click_id).checked) == false) ?  'none' : ''
    }
    //(document.getElementById(click_id).checked == false) ?  'none'               : '' : '';

 }

}

</script>
<?php
$sql = "SELECT kc.PCODE, kc.count_wip_chemical_weighing, kc.count_wip_compounding, kc.count_wip_extrusion,
kc.count_wip_forming, kc.count_wip_deflashing, kc.kanban, kc.virtual, p.max_lot, p.min_lot FROM kanban_checker kc
JOIN plan p ON kc.PCODE = p.PCODE  ORDER BY p.PCODE";
$result = mysql_query($sql, $con);

?><label>Display Details:</label><input  onclick='showDetails(this);' id='chkDetail'   type='checkbox' checked='checked' value='wip'/>
<?php
echo "<table id='kanban_list'>";
echo "<tr>
        <th> PCODE </th>
        <th> LOT CODE </th>
        <th> CHEMICAL WEIGHING </th>
        <th> COMPOUNDING </th>
        <th> EXTRUSION </th>
        <th> FORMING </th>
        <th> DEFLASHING </th>
        <th> KANBAN </th>
        <th> VIRTUAL </th>
        <th> MAX LOT </th>
        <th> MIN LOT </th>
     </tr>";
while($row = mysql_fetch_assoc($result)){
    echo "<tr>
        <td>$row[PCODE]</td>
        <td> </td>
       <!-- <td>$row[LOT_CODE]</td>   -->
        <td>$row[count_wip_chemical_weighing]</td>
        <td>$row[count_wip_compounding]</td>
        <td>$row[count_wip_extrusion]</td>
        <td>$row[count_wip_forming]</td>
        <td>$row[count_wip_deflashing]</td>
        <td>$row[kanban]</td>
        <td>$row[virtual]</td>
        <td>$row[max_lot]</td>
        <td>$row[min_lot]</td>
        </tr>";
$sql = "SELECT  kd.LOT_CODE, kd.wip_chemicalweighing, kd.wip_compounding, kd.wip_extrusion, kd.wip_forming, kd.wip_deflashing
FROM kanban_data kd JOIN plan p ON kd.PCODE = p.PCODE  ORDER BY p.PCODE";
$result_kanban_data = mysql_query($sql, $con);

while($row_data = mysql_fetch_assoc($result_kanban_data)){
    echo "<tr id='wip'>
    <td></td>
    <td>$row_data[LOT_CODE]</td>
    <td>$row_data[wip_chemicalweighing]</td>
    <td>$row_data[wip_compounding]</td>
    <td>$row_data[wip_extrusion]</td>
    <td>$row_data[wip_forming]</td>
    <td>$row_data[wip_deflashing]</td>
    </tr>";
}
}
echo "</table>";

?>

the problem is all lot code display in P35 same with P35M PCODE… i want is only lotcode which has PCODE P35 will display below P35 also all lotcode with P35M will display below P35M.

Thank you

5

this is absolutely horrible, horrible advice

using joins is much, much more efficient

6

I was referencing to this and many similar posts on other blogs:
http://www.mysqlperformanceblog.com/2006/06/09/why-mysql-could-be-slow-with-large-tables/

Some joins are also better than others. For example if you have star join with dimention tables being small it would not slow things down too much. On other hand join of few large tables, which is completely disk bound can be very slow.

One of the reasons elevating this problem in MySQL is lack of advanced join methods at this point (the work is on a way) – MySQL can’t do hash join or sort merge join – it only can do nested loops method which requires a lot of index lookups which may be random.

It is possible though that this info is outdated and I didnt hear about…
can you reference anything that profes this?

regards
Hensel

7

extremely possible

:cool:

8

But my join has a problem in displaying PCODE.

Thank you

9

So … GROUP BY p.pcode … ?

10

When I try GROUP BY, it did not work still P35M display in P35 also P35 display in P35M, and when I use GROUP BY the data display become 1 each PCODE type.

Thank you