QB: SELECT in the FROM clause possible?

Classic Servoy
1

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)

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,

2

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

3

Thanks for the hint, Patrick

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

4

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

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:

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,