aspose file tools*
The moose likes Object Relational Mapping and the fly likes HQL to get all the records in one table but in not other Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "HQL to get all the records in one table but in not other" Watch "HQL to get all the records in one table but in not other" New topic
Author

HQL to get all the records in one table but in not other

Nisha lakshminaraya
Ranch Hand

Joined: Feb 23, 2010
Posts: 30
Hello,

I am looking for HQL query to get all the records in first query which are not in second query.

For example:

select * from table where
column in
(select column from table where condition
GROUP BY column
having count(column)= 1

NOT IN

(select column from table
where condition
)
)
and condition

The query works in postgreSQL.

Here i am getting error like org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: not in near line 1

Please help me.


Thanks,
Aruna
Bill Gorder
Bartender

Joined: Mar 07, 2010
Posts: 1649
    
    7

Please UseCodeTags when posting code snippets.

You need to try to write this in HQL yourself first, if you get stuck people will be more than happy to help you, but we are NotACodeMill

I would start the reference docs to help get a basic understanding, there are also lots of examples.
http://docs.jboss.org/hibernate/orm/3.6/reference/en-US/html_single/#queryhql

Remember while similar to SQL it is not the same. You have to think in terms of your domain objects. For example you would not select * you would select your object.


[How To Ask Questions][Read before you PM me]
Nisha lakshminaraya
Ranch Hand

Joined: Feb 23, 2010
Posts: 30
I tried this query in HQL using NOT IN , except clause's but it still didnt work. After trying for three days i am posing this
Bill Gorder
Bartender

Joined: Mar 07, 2010
Posts: 1649
    
    7

Well not in is a valid HQL expression. ItDoesntWorkIsUseless, you have posted the SQL which is a good way to show us what you are trying to achieve. What we are missing is the HQL you are trying and a better description (like a stack trace) with the error you are getting. If you post the HQL that you are trying we may be able to give you pointers as to why it is not working.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: HQL to get all the records in one table but in not other