Hibernate version: 2.0 Alpha

Name and version of the database you are using: MSSQL 2005

How to create select like - "SELECT ... FROM Table1, Table2" using Nhibernate Criterias(in "from" clause tables must be separated with ",")

If you don't have an association between the two, you can't. If you only want to select one ot the tables, a subquery might work. have a look at this post:

http://forum.hibernate.org/viewtopic.php?t=988552&highlight=subquery+exists

generated query should exactly equal

SELECT p1.*
from Category as p1, Category as p2
where p1.LeftSide between p2.LeftSide and p2.RightSide
GROUP BY [p1].[Id]

but in NHiberante sources I found that "from" clause generation occurs with adding "on" expression

With criterias you need an association between the two entities. If you have one, you can use criterias and the resulting sql contains the necessary join (where the "on" comes from). If you don't have an association, you can't use criterias.