CODEWITHSUNDEEP
c#hibernatenhibernatehqlcriteria
AK_
AK_

Reputation: 8099

Nhibernate simple self join

I have a table with these fields:

ID (Primary key)
Name 
Some more data fields

I want to write a query that given a Name will give me all the rows with ID's bigger then the row with that name.

(Yes yes I know the name is not unique... in the system is.)

I want something like:

select *
From SomeTable as x
WHERE x.ID> (Select ID from SomeTable as y where y.Name LIKE :param)

or:

SELECT x
FROM SomeTable as x
JOIN SomeTable as y ON x.ID > Y.ID
WHERE Y.Name LIKE :Param

Of course I want the self join, and not the sub query.

BTW. Criteria goes too...

Upvotes: 1

Views: 847

Answers (1)

JB Nizet
JB Nizet

Reputation: 691635

You won't be able to do it with a join in HQL.

But this HQL query is OK:

select f from Foo f where f.id > (select f2.id from Foo f2 where f2.name = :name)

If the subselect might return several IDs, you may also use

select f from Foo f where f.id > all (select f2.id from Foo f2 where f2.name = :name)

Upvotes: 1

Related Questions