java ResultSet in a while loop doesnt work for some reason

Question

im trying to make a TableView named Overeenkomst that is has a ID and 2 other ID from 2 different tables called Gevonden and Vermiste.

the gimmick is that, once the ID Overeenkomst is called on, that it checks what the other 2 ID's are, checks the values from those 2 tables Gevonden and Vermiste from the other tables and sends those values back into the javafile and eventually a FXML file.

im trying to do this with make a ResultSet inside a while loop with the thought that it will loop the right ID value from the other table but it only works once. after that one time it stops.

Start.java

private void writeTableData() {
    Database database = new Database(
        "database",
        "localhost:3306",
        "root",
        " "
    );

    try {
        ResultSet resultOvereenkomst = database.executeQuery("SELECT * FROM testDatabase.Overeenkomst WHERE Gesloten = 1;");
        ResultSet resultGevonden = database.executeQuery("SELECT *  FROM testDatabase.Gevonden");




        while (resultOvereenkomst.next() && resultGevonden.next()) {
            Bagage bagage = new Bagage();
            bagage.setId(resultOvereenkomst.getInt("OvereenkomstID"));
            bagage.setDatum(resultOvereenkomst.getString("Datum"));

            ResultSet checkGevonden = database.executeQuery("SELECT Labelnummer, BagageType FROM testDatabase.Gevonden WHERE idGevonden = " + resultOvereenkomst.getInt("GevondenID") + ";" );

            bagage.setLabelNummer(checkGevonden.getInt("Labelnummer"));
            bagage.setBagageType(checkGevonden.getString("BagageType")); 
            geslotenData.add(bagage);

        }

    } catch (SQLException ex) {

    }

}

Bagage.java

public class Bagage extends Gebruiker{

private final SimpleIntegerProperty id = new SimpleIntegerProperty();
private final SimpleIntegerProperty gevondenID = new SimpleIntegerProperty();
private final SimpleIntegerProperty vermistID = new SimpleIntegerProperty();
private final SimpleStringProperty datum = new SimpleStringProperty();
private final SimpleIntegerProperty labelNummer = new SimpleIntegerProperty();
private final SimpleIntegerProperty vluchtNr = new SimpleIntegerProperty();
private final SimpleStringProperty bagageType = new SimpleStringProperty();
private final SimpleStringProperty tijd = new SimpleStringProperty();
private final SimpleStringProperty luchthaven = new SimpleStringProperty();
private final SimpleStringProperty bestemming = new SimpleStringProperty();
private final SimpleStringProperty merk = new SimpleStringProperty();
private final SimpleStringProperty kleur = new SimpleStringProperty();
private final SimpleStringProperty BijzondereKenmerken = new SimpleStringProperty();

//Setters
public void setId(int id){
    this.id.set(id);
}
public void setDatum(String datum){
    this.datum.set(datum);
}
public void setLabelNummer(int labelNummer){
    this.labelNummer.set(labelNummer);
}
public void setVluchtNr(int vluchtNr){
    this.vluchtNr.set(vluchtNr);
}
public void setBagageType(String bagageType){
    this.bagageType.set(bagageType);
}
public void setTijd(String tijd){
    this.tijd.set(tijd);
}
public void setLuchthaven(String luchthaven){
    this.luchthaven.set(luchthaven);
}
public void setBestemming(String bestemming){
    this.bestemming.set(bestemming);
}
public void setMerk(String merk){
    this.merk.set(merk);
}
public void setKleur(String kleur){
    this.kleur.set(kleur);
}
public void setBijzondereKenmerken(String bijzondereKenmerken){
    this.BijzondereKenmerken.set(bijzondereKenmerken);
}
public void setGevondenID(int gevondenID){
    this.gevondenID.set(gevondenID);
}
public void setVermistID(int vermistID){
    this.vermistID.set(vermistID);
}

//Getters
public Integer getId(){
    return id.get();
}
public String getDatum(){
    return datum.get();
}
public Integer getLabelNummer(){
    return labelNummer.get();
}
public Integer getVluchtNr(){
    return vluchtNr.get();
}
public String getBagageType(){
    return bagageType.get();
}
public String getTijd(){
    return tijd.get();
}
public String getLuchthaven(){
    return luchthaven.get();
}
public String getBestemming(){
    return bestemming.get();
}
public String getMerk(){
    return merk.get();
}
public String getKleur(){
    return kleur.get();
}
public String getBijzondereKenmerken(){
    return BijzondereKenmerken.get();
}
public Integer getVermistID(){
    return vermistID.get();
}
public Integer getGevondenID(){
    return gevondenID.get();
}

}

if i remove this piece of code:

ResultSet checkGevonden = database.executeQuery("SELECT Labelnummer, BagageType FROM testDatabase.Gevonden WHERE idGevonden = " + resultOvereenkomst.getInt("GevondenID") + ";" );

and change these two into:

bagage.setLabelNummer(resultGevonden.getInt("Labelnummer"));
bagage.setBagageType(resultGevonden.getString("BagageType")); 

Then it just works. but the problem is that they dont show the right values.

I looked awhile on Stackoverflow and havent found the answer i was looking for so i hope you guys could help me.

thanks for helping in advance

Answer 1

As the comment from e4c5 says, you should use join to link your tables, something like :

private void writeTableData() {
Database database = new Database(
    "database",
    "localhost:3306",
    "root",
    " "
);

try {
    ResultSet resultOvereenkomst = database.executeQuery("SELECT * FROM testDatabase.Overeenkomst LEFT JOIN testDatabase.Gevonden ON Gevonden.idGevonden = Overeenkomst.GevondenID  WHERE Gesloten = 1;");

    while (resultOvereenkomst.next()) {
        Bagage bagage = new Bagage();
        bagage.setId(resultOvereenkomst.getInt("OvereenkomstID"));
        bagage.setDatum(resultOvereenkomst.getString("Datum"));
        bagage.setLabelNummer(checkGevonden.getInt("Labelnummer"));
        bagage.setBagageType(checkGevonden.getString("BagageType")); 
        geslotenData.add(bagage);
    }

} catch (SQLException ex) {

}
}

For more informations on joins : http://www.sql-join.com/