Servoy Forum

I like to convert following SQL statement to Query Builder (simplified):

SELECT t.column1, t.column2 FROM (SELECT t.column1, column2 FROM table t INNER JOIN ... WHERE ...)

Tried something like the following but can't get access to what's after the FROM clause, i. e. datasources.db.hades.courses.createSelect() already implies the table (courses in this example)

Code: Select all

query = datasources.db.hades.courses.createSelect(subquery);
c = query.columns;

var subquery = datasources.db.hades.profile_positions.createSelect('pp');
var pp = subquery.columns;

query.result.addSubSelect(subquery);

Is there a way to write such a QB statement?

Regards,

I don't think you can do that. But you can construct everything as an IN-Query which supports subselects.

Thanks for the hint, Patrick

patrick wrote:I don't think you can do that. But you can construct everything as an IN-Query which supports subselects.

Tried this but can't get the OR with second SELECT. Any idea?

Code: Select all

SELECT\
      tl.id\
   FROM\
      timetable_lessons tl\
   WHERE\
      tl.period_school_year = " + periodSchoolYear + "\
      AND tl.period_fraction_name = '" + periodFractionName + "'\
      AND (tl.untis_lesson_id NOT IN (\
         SELECT\
            c.timetable_lesson_id\
         FROM\
            courses c\
          WHERE\
             c.period_school_year = " + periodSchoolYear + "\
             AND c.period_fraction_name = '" + periodFractionName + "')\
         OR tl.untis_lesson_id NOT IN (\
          SELECT\
             cc.timetable_lesson_id\
          FROM\
             class_courses cc\
          WHERE\
             cc.class_period_school_year = " + periodSchoolYear + "\
             AND cc.class_period_fraction_name = '" + periodFractionName + "'))\


Without the OR and second SELECT, it looks like this:

Code: Select all

var subquery1 = datasources.db.hades.courses.createSelect('c');
   var c = subquery1.columns;
   subquery1.result
      .add(c.timetable_lesson_id);   
   var subquery2 = datasources.db.hades.class_courses.createSelect('cc');
   var cc = subquery2.columns;
   subquery2.result
      .add(cc.timetable_lesson_id);   
   var subquery3 = datasources.db.hades.partial_class_courses.createSelect('pcc');
   var pcc = subquery3.columns;
   subquery3.result
      .add(pcc.timetable_lesson_id);
   
   var query = datasources.db.hades.timetable_lessons.createSelect('tl');
   var tl = query.columns;   
   query.result
         .addPk();
   query.where
      .add(tl.period_school_year.eq(periodSchoolYear))
      .add(tl.period_fraction_name.eq(periodFractionName))
      .add(tl.untis_lesson_id.not.isin(
         subquery1.where
            .add(c.period_school_year.eq(periodSchoolYear))
            .add(c.period_fraction_name.eq(periodFractionName)).root


But how to add subquery2 (and subquery3) with an OR?

Regards,