Results 1 to 19 of 19

Thread: Joining Tables

  1. #1
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default Joining Tables

    Hi, I am trying to join 3 tables, table2 and table3 is not connected but both should be connected to table1.

    table1 - is Product table, ex. a pair of shoes.
    table2 - is materials used to make the product.
    table3 - is labor for work in making leathers, ex. cutting of leather, shoe shining, shaping the shoes etc.

    I try to use Lef Join but it is not the expected output.
    Java Code:
    SELECT      product.ProductID, product.Description, product.Price, product.MatUsedID, 
                material.Mat_Code, material.Cost, product.LaborID, labor.Work, labor.Cost
    FROM        Product
    LEFT JOIN (Materials_Used materials) ON
               (product.MatUsedID = material.MatUsedID)
    LEFT JOIN (Product_Labor labor) ON
               (product.LaborID = labor.LaborID)
    WHERE       product.ProductID = 1158;


    I think joining table is not good for this situation so i just try to load each using simple select query but it gives me an error.
    "OUT OF MEMORY: Java heap space":D

    Here is the sample table, expected output and the actual output.
    Java Code:
    SAMPLE TABLE:
    PRODUCT				
    ProductID	Description         Price	MatUsedID	LaborID
    1158            Kids Shoes 7-10     125.45	255             963
    				
    MATERIALS USED				
    MatUsedID	Mat_Code	Cost		
    255             5879            0.25		
    255             2247            4.00		
    255             9746            1.50		
    255             3348            0.75		
    				
    PRODUCT LABOR				
    LaborID         Work                Cost		
    963             Leather Cutting     2.00		
    963             Shaping             0.85		
    963             Sole Cutting        5.50		
    963             Heating             1.00
    
    EXPECTED OUTPUT:
    |------------------ PRODUCT TABLE ---------------------| |- MATERIALS USED ---|  |--- LABOR ------------------|
    |ProductID	Description         MatUsedID	LaborID| |MatUsedID   Mat_Code|  |  LaborID  Work             |
    |1158           Kids Shoes 7-10     255         963    | | 255         5879   |  |   963     Leather Cutting  |
    |1158           Kids Shoes 7-10     255         963    | | 255         2247   |  |   963     Shaping          |
    |1158           Kids Shoes 7-10     255         963    | | 255         9746   |  |   963     Sole Cutting     |
    |1158           Kids Shoes 7-10     255         963    | | 255         3348   |  |   963     Heating          |
    |-------------------------------------------------------------------------------------------------------------|
    
    ACTUAL OUTPUT:
    |------------------ PRODUCT TABLE ---------------------| |- MATERIALS USED ---|  |--- LABOR ------------------|
    |ProductID	Description         MatUsedID	LaborID| |MatUsedID   Mat_Code|  |  LaborID  Work             |
    |1158           Kids Shoes 7-10     255         963    | | 255         5879   |  |   963     Leather Cutting  |
    |1158           Kids Shoes 7-10     255         963    | | 255         5879   |  |   963     Shaping          |
    |1158           Kids Shoes 7-10     255         963    | | 255         5879   |  |   963     Sole Cutting     |
    |1158           Kids Shoes 7-10     255         963    | | 255         5879   |  |   963     Heating          |
    |-------------------------------------------------------------------------------------------------------------|
    |1158           Kids Shoes 7-10     255         963    | | 255         2247   |  |   963     Leather Cutting  |
    |1158           Kids Shoes 7-10     255         963    | | 255         2247   |  |   963     Shaping          |
    |1158           Kids Shoes 7-10     255         963    | | 255         2247   |  |   963     Sole Cutting     |
    |1158           Kids Shoes 7-10     255         963    | | 255         2247   |  |   963     Heating          |
    ....
    |-------------------------------------------------------------------------------------------------------------|
    Im am sorry if this explanation is too long this is the best way I can explain it.

    And Thanks for any help
    Last edited by mine0926; 09-18-2010 at 08:12 AM. Reason: add code tags

  2. #2
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,925
    Rep Power
    19

    Default

    Joining is what you want to do, however your table structure is wrong to get the first result.

    The second result is correct since there is no relationship between Material and Labor. That means the join between product and material results in one row for each material, and then those rows are each joined with labor, resulting in 4 rows for each material for each labor. You need to sort your tables out before you can get the result you want from the database.

  3. #3
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    Im sorry but what kind of sorting will I do to achieve the result that I want?

    Thanks for explaining it to me.

  4. #4
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    You need to provide some sort of "common" field in the labor and material db to acheive the 1-to-1 relationship you're looking for. More flexible, however, would be to create a fourth table to hold these 1-to-1 relationships in order to be able to use the same "material" in multiple "labors" or multiple "materials" in one labor, when necessary. Then, once you have decided upon, and implemented, one of those options, you can expand your query to take it into account.

  5. #5
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,925
    Rep Power
    19

    Default

    Yep, there needs to be some way for the database to recognise the relationship you are describing. At the moment there is nothing there.

  6. #6
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    Materials and Labors is NOT connected to each other. Materials is connected in Products, Labors is connected to Product
    but materials and labors are not. I am starting to realize that this cannot be done in one query since materials and labor is not
    connected to each other and will just give an full join.

    I am not sure but I think I don't really need to use JOIN because I just need to load it in its JTextField for editing purposes. So
    I can use seperate select query for product, materials used and product labor, but if there is a query that can give the output that
    I want then it is much easier.

    Here are the other things that I tried.
    I try this one but it gives me error. OUT OF MEMORY: Java Heap Space, I already mention this above.
    Java Code:
            Connection conn = null;
            String url = "jdbc:mysql://MainComputer:3306/DBProducts";
            try
            {   Class.forName("com.mysql.jdbc.Driver").newInstance();
                conn = DriverManager.getConnection(url, "username", "password");
                //System.out.println("Connection Successful");
                Statement statement = conn.CreateStatement();
    
                try
                {   String product_query = "SELECT ProductID, Description FROM Product "
                            + "WHERE ProductID = '" + txtProductID.getText() + "'";
                    ResultSet product_result = statement.executeQuery(product_query);
                    .. //Load to JTextFields
                    product_result.close
    
                    String mat_query = "SELECT MatUsedID, Mat_Code FROM Materials_Used "
                            + "WHERE MatUsedID = (SELECT MatUsedID FROM Product "
                            + "WHERE ProductID = '" + txtProductID.getText() + "')";
                    ResultSet mat_result = statement.executeQuery(mat_query);
                    .. //Load to JTable
                    mat_result.close
    
                    String labor_query = "SELECT LaborID, Work FROM Product_Labor "
                            + "WHERE LaborID = (SELECT LaborID FROM Product "
                            + "WHERE ProductID = '" + txtProductID.getText() + "')";
                    ResultSet labor_result = statement.executeQuery(labor_query);
                    .. //Load to JTable
                    labor_result.close
                    statement.close
                }
                catch(Exception ex)
                {   ex.printStackTrace();
                }
            }
            catch(Exception ex)
            {   ex.printStackTrace();
            }

    I also try the code below but it gives me an error... Can not issue SELECT via executeUpdate()
    Java Code:
            Connection conn = null;
            String url = "jdbc:mysql://MainComputer:3306/DBProducts";
            try
            {   Class.forName("com.mysql.jdbc.Driver").newInstance();
                conn = DriverManager.getConnection(url, "username", "password");
                //System.out.println("Connection Successful");
                Statement statement = conn.CreateStatement();
    
                try
                {   String product_query = "SELECT ProductID, Description FROM Product "
                            + "WHERE ProductID = '" + txtProductID.getText() + "'";
    
                    String mat_query = "SELECT MatUsedID, Mat_Code FROM Materials_Used "
                            + "WHERE MatUsedID = (SELECT MatUsedID FROM Product "
                            + "WHERE ProductID = '" + txtProductID.getText() + "')";
    
                    String labor_query = "SELECT LaborID, Work FROM Product_Labor "
                            + "WHERE LaborID = (SELECT LaborID FROM Product "
                            + "WHERE ProductID = '" + txtProductID.getText() + "')";
    
                    statement.addBatch(product_query);
                    statement.addBatch(mat_query);
                    statement.addBatch(labor_query);
                    statement.executeBatch();
    
                    ResultSet product_result = statement.getResultSet();
                    //Load to JTextField
                    boolean moreResult1 = statement.getMoreResults();
                    if(moreResult1) //resultset of materials used
                    {   //Load to JTable
                    }
    
                    boolean moreResult2 = statement.getMoreResults();
                    if(moreResult2) //resultset of product labor
                    {   //Load to JTable
                    }
    
                    product_result
                    statement
                }
                catch(Exception ex)
                {   ex.printStackTrace();
                }
            }
            catch(Exception ex)
            {   ex.printStackTrace();
            }
    
            try
            {   if(conn != null){conn.close();}
            }
    Now I am trying to put them in seperate methods, each method connect to database, use select query then
    load the resultset to JTextField/JTable and close the database. I am not sure with this, it bothers me,
    I dont think this is the right thing to do..

    Thanks,
    geje

  7. #7
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,925
    Rep Power
    19

    Default

    You don't batch select statements.
    They're for inserts and updates only.

    Also, since you have a reason for the output you want then there must be a relationship between materials and product. I'm sorry but why is this output correct?
    Java Code:
    EXPECTED OUTPUT:
    |------------------ PRODUCT TABLE ---------------------| |- MATERIALS USED ---|  |--- LABOR ------------------|
    |ProductID	Description         MatUsedID	LaborID| |MatUsedID   Mat_Code|  |  LaborID  Work             |
    |1158           Kids Shoes 7-10     255         963    | | 255         5879   |  |   963     Leather Cutting  |
    |1158           Kids Shoes 7-10     255         963    | | 255         2247   |  |   963     Shaping          |
    |1158           Kids Shoes 7-10     255         963    | | 255         9746   |  |   963     Sole Cutting     |
    |1158           Kids Shoes 7-10     255         963    | | 255         3348   |  |   963     Heating          |
    |-------------------------------------------------------------------------------------------------------------|
    There is nothing in the data in the tables to suggest this relationship. Why is Matreial code 5897 only related to Leather Cutting? There is nothing in your data to imply that relationship. Until you can say why that is the case then you will not get an answer.

  8. #8
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    EDT - Sorry I paste the worng reply...

    Actually I dont really need to load that way. I was just thinking if that is
    possible but now I know it cannot be because the Materials is not connected to Labor.

    I was able to load the data in seperated methods. Each method connect to database, use select query then
    load the resultset to JTextField/JTable and close the database.
    Java Code:
     private void LoadProduct(){
        //connect to database
        //a SELECT * FROM Products Where ProductID = '" + txtProductID.getText() + "'";
        //Load to JTextFields.
        //close connection
     }
    
     private void LoadMatUsed(){
        //connect to database
        //a SELECT * FROM Material_USed Where MatUsedID =
                (SELECT MatUsedID FROM Products Where ProductID = '" + txtProductID.getText() + "')";
        //Load to JTable.
        //close connection
     }
    
     private void LoadLabor(){
        //connect to database
        //a SELECT * FROM Labors Where LaborID =
                (SELECT LaborID FROM Products Where ProductID = '" + txtProductID.getText() + "')";
        //Load to JTable.
        //close connection
     }
    You think this is the best way?

    Thanks again :),
    geje
    Last edited by mine0926; 09-21-2010 at 09:56 AM.

  9. #9
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,925
    Rep Power
    19

    Default

    And?
    That is utterly irrelevant to my question.

  10. #10
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    I think you read the wrong one.. Im sorry. My mistake.

  11. #11
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,925
    Rep Power
    19

    Default

    Do you need all the data in one go?

    Without knowing what it is you are doing, in terms of what this data is for, I cannot say if that is good or not. Normally loading everything into memory is not a Good Thing.

  12. #12
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    Do you need all the data in one go?
    No.

    Without knowing what it is you are doing, in terms of what this data is for, I cannot say if that is good or not. Normally loading everything into memory is not a Good Thing.
    1. User input ProductID.
    2. User click EDIT Button.
    3. Start to Query.
    4. If result is greater than 0 then Load to JTextField.

    I was just thinking that opening connection and closing it is not good. I read some forums before they said that
    opening and closing the database it not really good, they said the I should keep the database open and close it when I am done.
    At post#10, the sample code opent then close the database three(3) times.

    The 3 methods at post#10 will be called in an actionPerformed of a JButton.
    Java Code:
     private void jButton1ActionPerformed(java.awt.event.ActionEvent evt){
        loadProduct()
        loadMatUsed()
        loadLabor();
    }
    Thanks for always helping me, :)
    geje

  13. #13
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Quote Originally Posted by mine0926 View Post
    I was just thinking that opening connection and closing it is not good. I read some forums before they said that
    opening and closing the database it not really good, they said the I should keep the database open and close it when I am done.
    Okay? And there's no reason that you can't do three queries using a single connection (even in parallel since they are only selects). Also, you could simply use a Connection pool, in which you're "opening" and "closing" a connection (from your program) isn't really opeinging and closing connections, simply retrieving and returning them to/from the pool.

  14. #14
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,925
    Rep Power
    19

    Default

    OK.
    I still don't really know what those three chunks of data are for, so the three queries may or may not make sense.

    Assuming that makes sense, though, you should be using a PreparedStatement, and not building your query by concatenating Strings.

    Oh, and what masijade says re: single connection.

  15. #15
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    Thank you for your replies and suggestions/advice. I try the single connection(i think it is different from the single connection that you were telling me) but it gives me error as I mentioned above.
    I will research about connection pool, I think it will help me a lot. Do you know any website that explain well and has an simple example of connection pool can you

    Thanks again. I really appreciate your replies.

  16. #16
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,925
    Rep Power
    19

    Default

    Which error?
    The OutOfMemory one?

    That seems odd since you're not getting much data...

    How much memory have you given Java?

  17. #17
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    I did not set any memory for java..

    Thanks,
    geje

  18. #18
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,925
    Rep Power
    19

    Default

    You must be doing something else that's causing the OOM then. That is, if the example data you gave above is the actual amount you'd be loading up with those queries.

  19. #19
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    Sorry for late late reply. I saw that I am trying to load everything after
    the example code above. Thanks for helping me.

Similar Threads

  1. Replies: 7
    Last Post: 04-18-2010, 07:09 AM
  2. Java Tables
    By greatmajestics in forum AWT / Swing
    Replies: 2
    Last Post: 03-25-2010, 05:03 PM
  3. Help Printing Tables
    By ogidantunde in forum Advanced Java
    Replies: 0
    Last Post: 06-14-2008, 06:34 PM
  4. Joining lines in Eclipse
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 12-04-2007, 11:17 AM
  5. Joining two 2D Arrays over a common column
    By Sknight126 in forum Advanced Java
    Replies: 0
    Last Post: 11-02-2007, 11:29 AM

Posting Permissions

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