NHibernate version: 1.2

I have a problem with CreateSqlQuery.
my sql query (that works is) :

select * from dbo.Enrollments as e where
e.LectureId in
(select l.Id from dbo.Lectures as l where l.TeacherId=2)

i was able to get the first select running, the code is :

string queryStr = "select {E.*} from Enrollments {E} where {E}.LectureId = 2";
IQuery query = _session.CreateSQLQuery(queryStr, "E", typeof(Enrollment));

can anyone please tell me how to write the whole query.

what i have written so far is :

string queryStr = "select {E.*} from Enrollments {E} where {E}.LectureId in ( select {L}.Id from Lectures {L} where {L}.TeacherId=" + _request.TeacherId + " )";

i have tried also with :
IQuery query = _session.CreateSQLQuery(queryStr, new string[] { "E", "L" }, new Type[] { typeof(Enrollment), typeof(Lecture)

Exception MESSAGE is : Incorrect syntax near '}'.

thank you,
Horea

Since you aren't actually retrieving any lectures, you don't need to use {L} but instead you can use just the table name, i.e. select {E.*} from Enrollments {E} where {E}.LectureId in (select l.Id from dbo.Lectures as l where l.TeacherId=2).