57, 57 An identification variable must be defined for a JOIN expression

Question

I am getting the error

[57, 57] An identification variable must be defined for a JOIN expression

when trying to run this query

    public String searchString = "test";

    public List<Appointment> appointmentRangeSearch(Date startdatetime, Date endDate) {
        Query q = em.createQuery("SELECT u FROM Appointment U INNER JOIN Users_appointment "
                + "ON u.userid = users_appointment.userid"
                + " WHERE u.startDatetime BETWEEN :date1 AND :date2 "
                + "AND u.ATTENDEES_USER_NAME LIKE :search");
//Query q = em.createQuery("SELECT u FROM Appointment U WHERE u.startDatetime BETWEEN :date1 AND :date2");
        q.setParameter("search", "%" + searchString + "%");
        q.setParameter("date1", startdatetime, TemporalType.TIMESTAMP);
        q.setParameter("date2", endDate, TemporalType.TIMESTAMP);
        return q.getResultList();
    }

What is causing this? How can it be fixed?

Answer 1

Maybe, beacuse you have different table names:

INNER JOIN Users_appointment

AND

users_appointment.userid

Answer 2

Don't see anything wrong... try making U lower case and use

SELECT u.* FROM Appointment u....

em.createQuery("SELECT u.* FROM Appointment u INNER JOIN Users_appointment "
                + "ON u.userid = users_appointment.userid"
                + " WHERE u.startDatetime BETWEEN :date1 AND :date2 "
                + "AND u.ATTENDEES_USER_NAME LIKE :search");