hi all,

i am having a sql query like :

SELECT c.course_id, c.course_name, c.subject
FROM course c,
(SELECT course_id, course_type, course_cost
FROM course_cost_detail det, course cr
WHERE cr.course_id = det.course_id AND course_type = 'crash') crash,
(SELECT course_id, course_type, course_cost
FROM course_cost_detail det, course cr
WHERE cr.course_id = det.course_id AND course_type = 'regular') regular
WHERE c.course_id = crash.course_id(+)
AND c.course_id = regular.course_id(+)

NB : The course_id and course_type form a composite key in course_cost_detail table and course_id is the primary key in course table(also a foreign key in course_cost_detail)

can anybody let me know how to write a Criteria or HQL for above query.

Thanks in advance,

Loganathan.K