Search code examples
javajdbcprepared-statementresultset

How to retrieve values from Foreign Key table?

I'm trying to get the values of each column of my Foreign Key table. So I normalize my table using Second Normal Form where I split my tables into two. Picture below.

1st Table

1st Table where my Primary Key is SECTION_ID referencing SECTION_ID in the 2nd Table.

2nd Table

Here where I supply the values for the SECTION_NAME to search if the record exists. What I want to do if the user enter a existing record I want to get all the values of Foreign Key table. How will I do it?

Code

private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {                                         
    String searchSection = Section_SearchSection_Textfield.getText();
    String searchStudentLimit = Section_Student_Limit_ComboBox.getSelectedItem().toString();

    String searchSECTION_NAME = "SELECT * FROM allsections_list WHERE SECTION_NAME = ?";

    try (Connection myConn = DBUtil.connect();
             PreparedStatement myFirstPs = myConn.prepareStatement(searchSECTION_NAME);)
        {
             myFirstPs.setString(1, searchSection);

             try (ResultSet myRs = myFirstPs.executeQuery())
             {
                 int resultCounter = 0;
                 while (myRs.next())
                 {
                     String mySectionName = myRs.getString(2);//Get the value of SECTION_NAME
                     Section_SectionName_TextField.setText(mySectionName);
                     Section_SectionName_TextField.setEnabled(true);
                     resultCounter++;
                 }
                 if (resultCounter == 1)//If exist
                 {
                     JOptionPane.showMessageDialog(null, "Data Found");
                 }
                 else//If not exist
                     JOptionPane.showMessageDialog(null, "No Data Found");
             }}

Do I need to create a another select query for Foreign Key? I think it's no because I just wanna get the values. Correct me if I'm wrong. Feel free to comment. Thanks! :)

Solution

  • The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

    Check this SQL Left JOIN

    Use select * from allsections_list A left outer join allsections_settings S on A.Section_ID = S.Section_ID and get the values you need