[Logo]
Forums Register Login
Hibernate Desing issue
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
Try a correlated subquery. Something like this maybe:

from Employee as employee
where not exists (
from EmpGroup as eg where eg.Employee = employee
)
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


thanks in advance


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.
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.
thanks in advance
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
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
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 ]
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

This thread has been viewed 2301 times.

All times above are in ranch (not your local) time.
The current ranch time is
Nov 15, 2018 15:43:16.