Servoy Forum

Hi

I am looking for the Query Builder notation when a JOIN, in my example the LEFT OUTER JOIN, consists of one or more joins as in the example below where the LEFT OUTER JOIN contains two INNER JOIN?

How to formulate this for the Query Builder? May be there is an easier way to achieve the same result, may be the solution is with subqueries?

Following is the current code (part of the whole SQL statement)

Code: Select all

var query = "\
   SELECT DISTINCT\
      cl.number\
   FROM\
      class_members cm\
      INNER JOIN profile_positions pp\
         ON cm.natural_person_id = pp.natural_person_id\
         AND cm.class_period_fraction_name = pp.period_fraction_name\
         AND cm.class_period_school_year = pp.period_school_year\
      LEFT OUTER JOIN (courses c\
         INNER JOIN teaching_allotments tac\
            ON c.subject_code = tac.course_subject_code\
            AND c.period_fraction_name = tac.course_period_fraction_name\
            AND c.period_school_year = tac.course_period_school_year\
            AND c.code = tac.course_code\
         INNER JOIN persons ct\
            ON ct.id = tac.natural_person_id)\
         ON c.code = pp.course_code\
         ...


Thanks and best regards,

Hi All

As I could not find a hint in the documentation for this, I am coming back to ask if anyone is having a QB statement fulfilling this requirement willing to share?

Thanks in advance and regards,

Hi Huber,

This is simple example of how you can convert the raw sql to QB.

var query = datasources.db.your_server.class_members.createSelect("cm");
query.result.add(query.columns.number).distinct;
var join1 = query.joins.add(datasources.db.your_server.profile_positions.getDataSource(), JSRelation.INNER_JOIN, 'pp');
join1.on.add(query.columns.natural_person_id.eq(join1.columns.natural_person_id)).add(query.columns.class_period_fraction_name.eq(join1.columns.period_fraction_name));

For more information please watch our webinar series about QB: https://www.youtube.com/watch?time_cont ... xPqhJZtr4A
and look at our wiki page: https://wiki.servoy.com/display/DOCS/QBSelect

Let me know if you have further questions.