Htykuut

I'm having some trouble figuring out how to alter my existing sql query so that it will allow me to display records from a table in my jdbc database based on data from other tables in the database. I will illustrate my problem below:

Example of "DEMANDS" table (column headers, "ID" is the primary key):

NAME|ADDRESS|DESTINATION|DATE|TIME|ID

Example of "DRIVERS" table ("REGISTRATION" is the primary key):

USERNAME|PASSWORD|REGISTRATION|NAME

Example of "JOURNEY" table ("JID" is the primary key,"REGISTRATION" is a foreign key)

JID|NAME|ADDRESS|DESTINATION|DISTANCE|REGISTRATION|DATE|TIME|STATUS

Below is the code I have that is used to display tables on a jsp file:

public String retrieve(String query) throws SQLException {

select(query);

return makeTable(rsToList());//results;

}



private void select(String query){        

    try {

        statement = connection.createStatement();

        rs = statement.executeQuery(query);

        //statement.close();

    }

    catch(SQLException e) {

        System.out.println("way way"+e);

        //results = e.toString();

    }

}



private String makeTable(ArrayList list) {

    StringBuilder b = new StringBuilder();

    String row;

    b.append("<table border="3">");

    for (Object s : list) {

      b.append("<tr>");

      row = (String) s;

        for (String row1 : row) {

            b.append("<td>");

            b.append(row1);

            b.append("</td>");

        }

      b.append("</tr>n");

    } // for

    b.append("</table>");

    return b.toString();

}//makeHtmlTable



private ArrayList rsToList() throws SQLException {

    ArrayList aList = new ArrayList();

    ResultSetMetaData metaData = rs.getMetaData();

    int count = metaData.getColumnCount(); //number of column

    String columnName = new String[count];



    for (int i = 1; i <= count; i++)

    {

        columnName[i-1] = metaData.getColumnLabel(i);

    }

    aList.add(columnName);



    int cols = rs.getMetaData().getColumnCount();

    while (rs.next()) { 

      String s = new String[cols];

      for (int i = 1; i <= cols; i++) {

        s[i-1] = rs.getString(i);

      } 

      aList.add(s);

    } // while    

    return aList;

} //rsToList

The query that I have is:

SELECT Drivers.Name, Drivers.Registration 

FROM Drivers 

LEFT JOIN Journey ON Journey.Registration = Drivers.Registration 

LEFT JOIN Demands ON Demands.Time = Journey.Time 

WHERE Demands.id IS NULL

What this query does is list drivers from the driver table that are available at the time specified in the demand (meaning their registration is not currently in a record in the journey table at the same time as the demand).

I cannot seem to figure out how I can alter the query to take into account dates as well so that the drivers that will be displayed will be ones that are available at the time and date specified in the demand as oppose to just the time (meaning their registration is not currently in a record in the journey table at the same time and date as the demand).

For example, currently if a driver in the journey table has a booking for 9:30 on 2018-11-22, they will not be displayed for a demand that has a date of 2018-11-25 at 9:30 due to the driver already having a booking at 9:30 even though they are on separate dates.

I have tried:

SELECT Drivers.Name, Drivers.Registration 

FROM Drivers 

LEFT JOIN Journey ON Journey.Registration = Drivers.Registration 

LEFT JOIN Demands ON Demands.Date = Journey.Date 

LEFT JOIN Demands ON Demands.Time = Journey.Time 

WHERE Demands.id IS NULL

However, this gives me a null pointer exception error, any help on this would be greatly appreciated!

Cheers,