Select data from two tables using SQL

Question

I have two tables linked by another table like this:

ROLES(RoleID, RoleName)
EMPLOYEES(EmployeeID, E_Name, Address)
ROLE_EMPLOYEES(RoleID#,EmployeeID#).

I want a query that retrieves all from EMPLOYEES and RoleID from ROLES and displays on Java form.

I have tried this but does not work:

rs=st.executeQuery("SELECT EMPLOYEES.*, ROLES.* FROM EMPLOYEES JOIN ROLES");               

while(rs.next()){
    //MOVE THE CURSOR TO THE FIRST RECORD AND GET DATA
    int employeeid=rs.getInt("EmployeeID");
    String id=Integer.toString(employeeid);
    String name=rs.getString("E_Name");
    String addr=rs.getString("Address");
    String s = rs.getString("RoleID");
    jComboBox1.addItem(s.trim());

    //DISPLAY THE FIRST RECORD IN THE TEXT FIELD
    txtEmpNumber.setText(id);
    txtEmpName.setText(name);
    txtEmpAddress.setText(addr);
    jComboBox1.setSelectedItem(s);
}

Luis Quijada · Accepted Answer · 2012-09-07 00:41:05Z

2

You may try this:

SELECT
   EM.*, RL.*
FROM
   EMPLOYEES EM
INNER JOIN
   ROLE_EMPLOYEES REM ON REM.EmployeeID = EM.EmployeeID
INNER JOIN
   ROLES RL ON RL.RoleID = REM.RoleID

Just by writing the keyword JOIN the db-engine does not know in which way it should join the data of the tables; unless you want to retrieve a cartesian product (that's not your case), you need to explicitly set the criteria by using the ON clause.

edited Sep 7, 2012 at 0:41

answered Sep 7, 2012 at 0:10

Luis Quijada

2,4051 gold badge27 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Luis Quijada Over a year ago

You're welcome. Please don't forget to mark it as accepted answer if It does solve your problem/question.

Collectives™ on Stack Overflow

Select data from two tables using SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related