MySQLSyntaxErrorException: Unknown column ' ____ ' in 'field list'

Question

I get the following MySQL exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'book.call_Number' in 'field list'.

What does that mean and how can I solve it?

Here is the code responsible for this exception:

public void actionPerformed(ActionEvent e) {
    list.clearSelection();

    String selectString = " ";
    String afName = auth_fName.getText();
    String aMI = auth_MI.getText();
    String alName = auth_lName.getText();
    String tField = titleField.getText();
    String sField = subjectField.getText();

    try {
        Connection conn = Database.getConnection();

        Statement s = conn.createStatement();

        if (!afName.equals("") && (!aMI.equals("")) && (!alName.equals("")) && (!tField.equals("")) && (!sField.equals(""))) {
            selectString = "SELECT a.call_Number as callNbr "
                + "FROM book a "
                + "FULL JOIN transaction b "
                + "ON a.call_Number=b.call_Number";
        }

        s = conn.createStatement();
        System.out.println(selectString);
        ResultSet rs = s.executeQuery(selectString);

        while (rs.next()) {
            String call_Num = rs.getString("call_Number");
            String title = rs.getString("title");
            String auth_lName = rs.getString("auth_lName");
            String auth_MI = rs.getString ("auth_MI");
            String auth_fName = rs.getString("auth_fName");
            String availability = rs.getString("availability");

            view = new View(call_Num, title, auth_lName, auth_MI, auth_fName, availability);
            vList.add(view);
            System.out.println(view);
        }

        rs.close();
        s.close();
        conn.close();

        list.setListData(vList.toArray());
    } catch (Exception ex) {
        ex.printStackTrace();
    }
}

Here is the DDL and content:

s.executeUpdate (
    "CREATE TABLE book ("
    + "call_Number CHAR(10),"
    + "PRIMARY KEY (call_Number),"
    + "auth_fName CHAR(30)NOT NULL, auth_MI CHAR(2),"
    + "auth_lName CHAR(50)NOT NULL, title CHAR(100) NOT NULL,"
    + "subject CHAR(30) NOT NULL)");
count = s.executeUpdate (
    "INSERT INTO book"
    + " VALUES"
    + "('MY.111.000', 'Mark', 'M','Bradshaw','Mystery Under the Sun','mystery'),"
    + "('MY.111.001', 'Mark','','Twain','The Adventures of Huckleberry Finn','mystery'),"
    + "('SF.111.002', 'Kito', 'M','Bradford','Mr. Roboto','science fiction'),"
    + "('SF.111.003', 'Eric','','Laslow','Science Fiction - Can It Happen?','science fiction'),"
    + "('AV.111.004', 'Rashad','','Cheeks','Fire Under the Bridge','adventure'),"
    + "('AV.111.005', 'Samantha','A','Appleby','The Open Sea','adventure'),"
    + "('CO.111.006', 'Lindsey', '','Butterby','What? We cant spend anymore!?','comedy'),"
    + "('CO.111.007', 'Judy', 'S','Yates','So this is life?','comedy'),"
    + "('IN.111.008', 'Elizabeth', 'J','Lee','Mystery Under the Sun','international'),"
    + "('IN.111.009', 'Gabriella', 'M','Rodriguez','Love in Brazil','international')");

*******t_action table***************************

  //create transaction table
        s.executeUpdate (
            "CREATE TABLE t_action ("
            + "patron_ID CHAR(10) NOT NULL,"
            + "call_Number CHAR(10) NOT NULL, check_Out_Date DATE NOT NULL, check_In_Date DATE NOT NULL,"
            + "PRIMARY KEY (patron_ID, call_Number),"
            + "avail CHAR(15), total_Charge FLOAT)");

        count3 = s.executeUpdate (
            "INSERT INTO t_action"
            + " VALUES"
            + "('P222200000','MY.111.000','2011-03-08','2011-03-15','AVAILABLE',5.00),"
            + "('P222200001','MY.111.001','2011-03-31','2011-04-6','DUE 2011-04-6',5.00),"
            + "('P222200002','SF.111.002','2011-03-30','2011-04-5','DUE 2011-04-5',5.00),"
            + "('P222200003','SF.111.003','2011-03-29','2011-04-4','DUE 2011-04-4',5.00),"
            + "('P222200004','AV.111.004','2011-03-28','2011-04-3','DUE 2011-04-3',5.00),"
            + "('P222200005','AV.111.005','2011-03-27','2011-04-2','DUE 2011-04-2',5.00),"
            + "('P222200006','CO.111.006','2011-03-26','2011-04-1','DUE 2011-04-1',5.00),"
            + "('P222200007','CO.111.007','2011-01-06','2011-01-12','AVAILABLE',5.00),"
            + "('P222200008','IN.111.008','2011-02-06','2011-02-12','AVAILABLE',5.00),"
            + "('P222200009','IN.111.009','2011-03-06','2011-03-12','AVAILABLE',5.00)");

Answer 1

Use a <column> as predicate like below:-

selectString = "SELECT a.call_Number as callNbr, ... " + "FROM book a" + "FULL JOIN transaction b" + "ON a.call_Number=b.call_Number";

And then change the code to look for callNbr :-

String call_Num = rs.getString("callNbr");

HTH.

Answer 2

Change your query to this:

    selectString = "SELECT a.call_Number "
        + "FROM book a "
        + "INNER JOIN transaction b "
        + "ON a.call_Number=b.call_Number";

MySQL does not support FULL OUTER JOIN. If you really need the effect of that - you'll need 2 selects with a UNION. Although from looks of it - does not seem like that would be necessary.