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