Nsryjdtyk

I'm very new to using databases and SQL in general and I'm having some trouble figuring out a function that 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 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 that 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

All of this code works fine and if I pass in a query into the "Retrieve" function such as:

String query = "select * from DRIVERS";

It will display all of the records of the "DRIVERS" table.

What I am wanting to do though, is only 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) If possible, I would also only like to display the "NAME" and "REGISTRATION" columns as oppose to the whole record.

I would really appreciate some help with this as I've searched around for solutions for quite some time and have not been able to work out a function that will achieve the desired outcome.

Cheers,

Creation of tables script:

-- --------------------------------------------------------

--DROP Table Demands;

CREATE TABLE Demands (

  Name varchar(20),

  Address varchar(60),

  Destination varchar(60),

  Date date DEFAULT NULL,

  Time time DEFAULT NULL,

  Status varchar(15) NOT NULL,

  id INT primary key

);





-- --------------------------------------------------------

--DROP Table Drivers;

CREATE TABLE Drivers (

  username varchar(20),

  password varchar(20),

  Registration varchar(10),

  Name varchar(20),

  PRIMARY KEY (Registration)

);





-- --------------------------------------------------------



--DROP Table Journey;

CREATE TABLE Journey (

  jid INT primary key

  Destination varchar(60),

  Distance integer NOT NULL DEFAULT 1,

  Registration varchar(10) NOT NULL,

  Date date NOT NULL,

  Time time DEFAULT NULL

);

The following query may answer your question.

SELECT Drivers.Name, Drivers.Registration

FROM Drivers

LEFT JOIN Journey ON Journey.Registration = Drivers.Registration

LEFT JOIN Demands ON Demands.Date = Journey.Date

WHERE Demands.id IS NULL;

This joins JOURNEY and DRIVER based on the foreign key relation. It then outer-joins DEMANDS and JOURNEY based on an implicit relation that is DATE. Finally we only keep records that fail the outer join condition.

The model has a major flaw though as the relation between DEMANDS and JOURNEY is based on a field of type Date, as far as one can tell by what your provided.