Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10

Thread: Left Join

  1. #1
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Left Join

    I have gone through the database normalization process, and rewrote the queries in php to accomodate this, but now, in preparation for the next step, where the data can be retrieved, I need some help in understanding the LEFT JOIN.

    I had been advised that in the many=>many format, I should create a third table, but am now wondering if that was ONLY if my host supported INNODB table types.

    I have one table called workorder, where the basic information goes, and a second table where the part number and description of the parts required will go.

    The WorkOrder table has the following columns - work_id(primary key, auto-increment), work(work order number (in mmhhddss format), name, site, serial, severity, issue and sched(date)

    The Parts table has parts_id(primary key, autoincrement), number, description.

    Do I need to add another column to the Parts Table that would have a field that is the same as the workorder table? Perhaps the workorder # or use the primary key through the use of the mysql_insert_id().

    Originally I had created a third table called workorder_parts, but am thinking this is for tables that have INNODB type.

    Any direction on this would be appreciated.

    Ken

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    An inner join matches rows from two tables. an outer join ( the word outer is optional so you can have left join or left outer join) shows all rows even if there is no match in the second table.

    the type of join does not depend on which engine you use and can be used on any number of tables.

    it may still help to have the third table created.

  • #3
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Why would I want to use the third table, instead of just connecting directly to the workorder and parts table? Would there be a benefit to somehow using the owrkorder_parts table?

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    It all depends if the parts in your parts tables are going to be used for more than one workorder. If there are two workorders that need a Junction Fitter, which workorder ID do you put in the parts table? That's the reason for the third table.

    If the parts in your parts table are unique to one workorder, there's no need for a third table.

  • #5
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If I need to allow mutliple workorders to use a part number/name and have multiple parts in one workorder, I need to use the third table.

    I can find lots of tutorials and threads on selecting data using the JOIN function, but am not finding anything as far as inserting data into the third table (workorder_parts) to actually create the relationship between the workorder and the parts table.

    Would I have to do 2 INSERT statements since I need data from 2 different tables, and I also need to get this data after the initial information is posted to the DB as that is when the work_id and parts_id number will be created as it is autoincrementing.

    I have also noticed that each of the parts number/descriptions is generating an independent parts_id. Do I need to change this so all of the parts in one work order are under the same unique parts_id? I am thinking they should be individual so if I only want one part in a future workorder, I can get it based on the parts_id.


    thanks,

    Ken
    Last edited by kenwvs; 09-08-2006 at 08:03 PM. Reason: add info

  • #6
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am thinking that I just had a thought that may solve my problems....

    Do I only need to use the third table and a join statement when I want to select the data? I have been trying to figure out how to set it up when I do all the INSERTS, but now think I only need to tie them together when I am doing a SELECT query. Have I got this figured out finally?

    Ken

  • #7
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Unless I don't have your design in my head well enough, your parts table should have all of the parts in it already so no need to insert into that table. Actually it would be maintained by a different page altogether, similar to your employee table.

    So you have your parts table full of parts, now someone creates a workorder and chooses part_id 8302 and part_id 9309 as needed. You then

    1. Insert a row into workorder.
    2. Using the workorder ID you just inserted and the two part_ids that were chosen, insert two rows into workorder_parts. FYI, you can use the handy mysql_insert_id() function to get the just-inserted value of an auto-increment field.

    PHP Code:
    $query "INSERT into workorder .... (blah blah blah)";
    $result mysql_query($query);
    if (!
    $result) {
        die(
    'query failure! query: ' $query ' error message: ' mysql_error());
    }

    $newWorkOrderID mysql_insert_id($result);

    foreach (
    $_POST['partnumber'] as $pVal) {
        if (!empty(
    $pVal)) {
            
    $query "INSERT into workorder_parts
                (workorder_id, part_id)
                values($newWorkOrderID, $pVal)"
    ;
            
    $result mysql_query($query);
            if (!
    $result) {
                die(
    'query failure! query: ' $query ' error message: ' mysql_error());
            }
        }


  • #8
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Here's an example of selecting the data once you've inserted it. Personally I like to do two queries because it keeps things simple.

    The first query is easy-- just select the workorder. Then in your next query, select all the parts that are related to this workorder.
    PHP Code:
    //select workorder
    $query "SELECT workorder_id, ...(etc etc etc)
    FROM workorder
    WHERE workorder_id = $wo_id"
    ;
    $result mysql_query($query);
    if (!
    $result) {
        die(
    'query failure! query: ' $query ' error message: ' mysql_error());
    }

    $woInfo mysql_fetch_assoc($result);

    //select parts related to workorder
    $query "SELECT a.part_id, a.part_description (etc etc etc)
    FROM parts as a,
    workorder_parts as b
    WHERE a.part_id = b.part_id
    AND b.workorder_id = $wo_id"
    ;
    $result mysql_query($query);
    if (!
    $result) {
        die(
    'query failure! query: ' $query ' error message: ' mysql_error());
    }

    //There can be more than one part related to a workorder,
    //fetch them all with this loop
    for ($i 0$i mysql_num_rows($result); $i++) {
        
    $partInfo[$i] = mysql_fetch_assoc($result);
        
    //or, if you want all the data in the same array (I tend to do this):
        
    $woInfo['parts'][$i] = mysql_fetch_assoc($result);

    You now have the data in a PHP array which you can use however is needed for your page display. There's another way of writing the join, if you're more comfortable with it:

    PHP Code:
    $query "SELECT a.part_id, a.part_description (etc etc etc)
    FROM parts as a
    LEFT JOIN workorder_parts as b
    ON a.part_id = b.part_id
    WHERE b.workorder_id = $wo_id"


  • #9
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Fumigator:

    Thanks for the help. I think you have a fairly good understanding of how this all works, with only one problem. At this time there is not an actual database of the parts. As they create the workorder and determine which parts they need out of a german manual, they will input the parts number and description in the workorder itself. It isn't like there is a computerized parts list available. Having said that, I could make the parts a one=>one relationship, except down the road, as the parts are entered, it would be nice to be able to have them key in the part number and have the description show up, if it is already in the DB, and if not, it would get entered into the DB.

    It would be like, a SELECT from parts and if it isn't there, then INSERT into parts.

    Hope this makes sense.

    Ken

  • #10
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    In that case, it would be a simple matter to add an insert to the parts table if it is needed.


  •  

    Posting Permissions

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