4 tables Join and show it in Java MySQL-CodePudding

I have this project written in java. I want this thing show data from different table in 1 display.

Here's the java code

static void showPersonality() {
    try {          
        Statement stmt1 = conn.createStatement();
        ResultSet rs1 = stmt1.executeQuery("SELECT * FROM faculty JOIN teacher ON faculty.id=teacher.faculty_id JOIN department ON faculty.id=department.faculty_id JOIN course ON department.id=course.department_id");

        while (rs1.next()) {
           
            String namaFakultas = rs1.getString("faculty_name");
            String namaDosen = rs1.getString("teacher_name");
            String namaDepartemen = rs1.getString("department_name");
            String namaKursus = rs1.getString("course_name");
            System.out.println(String.format("| %s | %s | %s | %s |", namaFakultas, namaDepartemen, namaKursus, namaDosen));            
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

}

And I also made 4 table which is faculty, department, teacher, and course

Faculty table (I inserted 1 data)

CREATE TABLE faculty(
    id INT NOT NULL AUTO_INCREMENT,
    faculty_name VARCHAR(100) NOT NULL,
    PRIMARY KEY(id)
);

Department table (I inserted 2 data in 1 faculty)

CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `faculty_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `faculty_id` (`faculty_id`),
  CONSTRAINT `department_ibfk_1` FOREIGN KEY (`faculty_id`) REFERENCES `faculty` (`id`)
);

Teacher table (I inserted 2 data in 1 faculty with each data has different department)

CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_name` varchar(255) NOT NULL,
  `faculty_id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `department_id` (`department_id`),
  CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`),
  KEY `faculty_id` (`faculty_id`),
  CONSTRAINT `teacher_ibfk_2` FOREIGN KEY (`faculty_id`) REFERENCES `faculty` (`id`)
);

Course table (I inserted 2 data with each data has different department)

CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `course_name` varchar(255) NOT NULL,
  `department_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `department_id` (`department_id`),
  CONSTRAINT `course_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`)
);

After I execute the code, it shows 4 data

| Teknik | Informatika | OOP | Adi Gunawan |
| Teknik | Sistem Informasi | Basis Data | Adi Gunawan |
| Teknik | Informatika | OOP | Ani Wijaya |
| Teknik | Sistem Informasi | Basis Data | Ani Wijaya |

My expectation it only show 2 data with all different table except Faculty table

| Teknik | Informatika | OOP | Adi Gunawan |
| Teknik | Sistem Informasi | Basis Data | Ani Wijaya |

Is there anything wrong with my java code or maybe my database?

CodePudding user response：

(ouput is just show an list of name, not id. you should show all of raw datalist for correct answer)

(guess name values of other 3 tables are unique. but name of course table are not unique.)

i think it just the data problem.

in query, after front 3 joins are executed, maybe return 2 rows.

| Teknik | Informatika | OOP 
| Teknik | Sistem Informasi | Basis Data

and look at the final results after next 4th join step(reordered output)

| Teknik | Informatika | OOP | Adi Gunawan |
| Teknik | Informatika | OOP | Ani Wijaya |
| Teknik | Sistem Informasi | Basis Data | Adi Gunawan |
| Teknik | Sistem Informasi | Basis Data | Ani Wijaya |

as you can see, it means that each OOP and Basis Data joined with 2 rows of course table so raw data expect:

# rows in course table

... OOP_id | Adi Gunawan
OOP_id | Ani Wijaya
Basis Data_id | Adi Gunawan
Basis Data_id | Ani Wijaya

if you want,

| Teknik | Informatika | OOP | Adi Gunawan |
| Teknik | Sistem Informasi | Basis Data | Ani Wijaya |

on clause of 4th join, maybe not a department_id column, it must another column

if course data have faculty_id, join with faculty_id of two tables

edit.

after your comment, i found i missed one thing.

teacher table have two relationship

many to one relationship with faculty table.
many to one relationship with department table.

so in on clause after joining teacher, you should add condition that teacher's department_id = department's id too. not just faculty.id=teacher.faculty_id

query should be like

SELECT * 
FROM faculty 
JOIN department ON faculty.id=department.faculty_id
JOIN teacher ON faculty.id=teacher.faculty_id AND teacher.department_id = department.id
JOIN course ON department.id=course.department_id