Hibernate Desing issue

[sharaf navas]

Hi all i'm having problem in fetching data. where i've designed 3 tables
1)employee
-----------
empId,empName , empDOB

2)groups
-----------
gpId,gpName,gpDesc

3)emp_group
------------
id,empId,gpId

i'm having many-to-many relation here. n I'm assigning employee to some groups. So how do i write a HQL to get a employee who is not already in a group.

thanks in advance

[Scott Johnson]

Try a correlated subquery. Something like this maybe:

from Employee as employee
where not exists (
from EmpGroup as eg where eg.Employee = employee
)

[sharaf navas]

thanks scott
but i try to come up with SQL query to this scenario. but still having problem in transfering SQL to HQL any my SQL is here

 select distinct g.gpName from en_empGroup as eg inner join en_Group as g on g.gpId = eg.gpId where g.gpI
d not in (select gpId from en_empGroup where emp_Id=1);   

thanks in advance

[Scott Johnson]

 select distinct g.gpName from en_empGroup as eg inner join en_Group as g on g.gpId = eg.gpId where g.gpI
d not in (select gpId from en_empGroup where emp_Id=1);   

Does this sql work the way you want? It's different that what you described above.

This will the group names of all groups that employee #1 is not in.

I read the post to say get employees not already in a group.

Do you really want to join to en_Group in the outer select? That will limit your results to just groups that have one or more employees. Seems like you should eliminate that join.

The example I gave above can be adapted. The entity for en_empGroup will be in the outer from clause instead of Employee. And you'll have an additional where condition in the subselect for employee id = 1.

[sharaf navas]

thanks
the mySQL whic have given is da correct one. but i need that to translated in to HQL. since the mySQL code gives me the correct result for me so please help me in it.

 select distinct g.gpName from en_group as eg inner join en_Group as g on g.gpId = eg.gpId where g.gpId not in (select gpId from en_empGroup where emp_Id=1   

thanks in advance

[Vilpesh Mistry]

Hi sharaf
Try exceuting this query, if need be change it & check

select empId,empName,empDOB from employee emp
where emp.empId not in (
select egp.empId
from emp_group egp left fetch join groups grp
where egp.gpId = grp.gpId
)

Please post corrections if this query is wrongly constructed
thanks

[sharaf navas]

thanks vilpesh
actually my code is
startOperation();
Query qry=session.createQuery("select distinct g.gpName from EmpGroupVO eg inner join GroupVO g g.gpId join eg.gpId where g.gpId not in (select empg.gpId from EmpGroupVO empg where emp_id = 1)");
Obj=qry.list();

and I'm getting error saying

Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: g near line 1, column 90 [select distinct g.gpName from com.tt.notification.vo.EmpGroupVO eg inner join GroupVO g g.gpId join eg.gpId where g.gpId not in (select empg.gpId from com.tt.notification.vo.EmpGroupVO empg where emp_id = 1)]


Caused by: line 1:90: unexpected token: g
at org.hibernate.hql.antlr.HqlBaseParser.fromJoin(HqlBaseParser.java:1765)
at org.hibernate.hql.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1420)
at org.hibernate.hql.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1130)
at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:702)
at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:296)
at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:159)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:238)
... 16 more

i appritiate any one kindly help me regarding this.
thanks in advance

[Vilpesh Mistry]

hi,
i feel their is syntax error in the inner join.

on is absent after the 2 tables joined using inner join,

Please try as follows :
select distinct g.gpName from EmpGroupVO eg inner join GroupVO g
on g.gpId = eg.gpId
where g.gpId.....

Thanks
[ September 25, 2006: Message edited by: Vilpesh Mistry ]

[sharaf navas]

hi there
i think on shouldn't come because by inserting on i had this error.
aused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 89 [select distinct g.gpName from com.tt.notification.vo.EmpGroupVO eg inner join GroupVO g on g.gpId = eg.gpId where g.gpId not in (select empg.gpId from com.tt.notification.vo.EmpGroupVO empg where emp_id = 1)]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:244)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:155)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:109)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:75)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:54)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1583)
at com.tt.notification.dao.EmpGroupDAO.LoadGroup(EmpGroupDAO.java:121)
... 7 more
Caused by: line 1:89: unexpected token: on
at org.hibernate.hql.antlr.HqlBaseParser.fromJoin(HqlBaseParser.java:1765)
at org.hibernate.hql.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1420)
at org.hibernate.hql.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1130)
at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:702)
at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:296)
at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:159)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:238)
Thats why didn't insert any ON there
thanks