SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem in joining three tables

    Hi..

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

    Code:
    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;
    Code:
    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
    Code:
    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
    Attached Files Attached Files

  2. #2
    SitePoint Member
    Join Date
    Feb 2012
    Location
    Switzerland
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you give me an example.

    Thank you

  4. #4
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried this:

    Code:
    <?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. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by simpeligent View Post
    but in general I suggest you skip the joins and make the "joining" with php
    this is absolutely horrible, horrible advice

    using joins is much, much more efficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Feb 2012
    Location
    Switzerland
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was referencing to this and many similar posts on other blogs:
    http://www.mysqlperformanceblog.com/...-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. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by simpeligent View Post
    It is possible though that this info is outdated ...
    extremely possible

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But my join has a problem in displaying PCODE.

    Thank you

  9. #9
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,246
    Mentioned
    16 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    I want to display PCODE once. like one P35, one P35M
    So ... GROUP BY p.pcode ... ?

  10. #10
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •