QB: SELECT in the FROM clause possible?

Questions and answers regarding general SQL and backend databases

QB: SELECT in the FROM clause possible?

Postby huber » Fri Mar 29, 2019 5:15 pm

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,
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm

Re: QB: SELECT in the FROM clause possible?

Postby patrick » Sun Mar 31, 2019 2:45 pm

I don't think you can do that. But you can construct everything as an IN-Query which supports subselects.
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: QB: SELECT in the FROM clause possible?

Postby huber » Mon Apr 01, 2019 12:03 pm

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.
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm

Re: QB: SELECT in the FROM clause possible?

Postby huber » Tue Apr 02, 2019 4:26 pm

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,
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 6 guests

cron