Query Builder (Sub)Query in a JOIN

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

Query Builder (Sub)Query in a JOIN

Postby huber » Tue Dec 19, 2017 11:48 am

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,
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: Query Builder (Sub)Query in a JOIN

Postby huber » Fri Jan 05, 2018 10:17 am

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,
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: Query Builder (Sub)Query in a JOIN

Postby mnaeimi » Fri Jan 05, 2018 12:06 pm

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.
Mina @ Servoy
mnaeimi
 
Posts: 26
Joined: Fri Jun 08, 2012 2:48 pm


Return to Programming with Servoy

Who is online

Users browsing this forum: Bing [Bot], Majestic-12 [Bot] and 9 guests