Hi,

Environment used: MySQL, GF3.1

I have these 3 tables namely Users,Customers and Tasks in my database.

Users will be assigned some tasks. A task will have a customer associated with it.

I am describing the tables ignoring the associations among them.The associations I care about are above.

Customer Table: ============ CUSTOMER_ID INT FIRST_NAME VARCHAR LAST_NAME VARCHAR HOME_NUMBER VARCHAR MOBILE_NUMBER VARCHAR EMAIL VARCHAR ADDRESS1 VARCHAR ADDRESS2 VARCHAR CITY VARCHAR STATE VARCHAR ZIP VARCHAR CUSTOMER_TYPE_ID SMALLINT

Users ==== ID INT USER_ID VARCHAR PASSWORD VARCHAR FIRST_NAME VARCHAR LAST_NAME VARCHAR CREATED_BY INT CREATED_TIME DATETIME LEVEL_ID SMALLINT

Tasks =====

TASK_ID INT CUSTOMER_TYPE_ID SMALLINT TASK VARCHAR TASK_DATE DATETIME IS_COMPLETED BIT ASSIGNED_TO INT CREATED_BY INT CREATED_AT DATETIME CUSTOMER_ID INT TASK_DESCRIPTION VARCHAR NOTES VARCHAR

I have entities created by Netbeans for all the tables in database.Now I am trying to execute a query which will give me some columns picked from Customers table and tasks table.I am trying to just create a normal join between these 2 tables, but I am stuck.I am trying this way:

@NamedQuery(name = "Task.findByUserId",query = "SELECT t.taskId, t.taskDate,t.isCompleted,t.taskDescription," + "c.firstName,c.lastName,c.email FROM Task t , Customer c " + "WHERE t.assignedTo.id= :assignedUserId AND c.customerId=t.customerId")

But it gets translated to :

Call: SELECT t0.TASK_ID, t0.TASK_DATE, t0.IS_COMPLETED, t0.TASK_DESCRIPTION,
t1.FIRST_NAME, t1.LAST_NAME, t1.EMAIL FROM TASKS t0, CUSTOMERS t2, CUSTOMERS t1
WHERE (((t0.ASSIGNED_TO = ?) AND (t1.CUSTOMER_ID = )) AND (t2.CUSTOMER_ID =
t0.CUSTOMER_ID))

What is the mistake I am doing here.Can some one correct me and also I would like to enquire about some documentation or book which explains JPQL nicely.

Thanks in advance. Sreekanth