Joining Tables

[mine0926]

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.

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.

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

[Tolls]

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.

[mine0926]

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

Thanks for explaining it to me.

[masijade]

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.

[Tolls]

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

[mine0926]

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.

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

        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

[Tolls]

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?

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.

[mine0926]

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.

 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

[Tolls]

And?
That is utterly irrelevant to my question.

[mine0926]

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

[Tolls]

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.

[mine0926]

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.

 private void jButton1ActionPerformed(java.awt.event.ActionEvent evt){
    loadProduct()
    loadMatUsed()
    loadLabor();
}

Thanks for always helping me, :)
geje

[masijade]

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.

[Tolls]

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.

[mine0926]

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.

[Tolls]

Which error?
The OutOfMemory one?

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

How much memory have you given Java?

[mine0926]

I did not set any memory for java..

Thanks,
geje

[Tolls]

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.

[mine0926]

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