Author Topic: sql view with multiple joins, self join, conditions, sorting  (Read 377 times)


  • New Member
  • *
  • Posts: 43
sql view with multiple joins, self join, conditions, sorting
« on: February 08, 2020, 11:03:43 am »
trying to create a view in firebird and sqlite3 as below, using single date field twice in same view.

you have 3 tables, a,b,c

a has dates
maybe field inc. date-id, date, type-id

b has date types - with recursion (eg.  meeting types ABC, tasks XYZ, subtasks X123 (ie. recursive sub-tasks of X))
maybe fields inc type-id, type-name, type-id2, status-id

c has status of each task.   (eg. todo, begun, ignore, completed)
maybe 2 fields - status-id, status-name

how would you create a view that can:

1.  Show 4 columns.     current task, with date deadline, next meeting, with date
2.  For column 1, how could you show recursive tasks within this view. 

Sample view below.

T task - deadline 12/12/20 -  meeting type 2 - on 13/12/20
X1 task - deadline 14/12/20 -  meeting type 1 - on 20/12/20   
X2 task - deadline 15/12/20 -  meeting type 1 - on 20/12/20
X task - deadline 16/12/20 -  meeting type 1 - on 20/12/20

(separate to the sql question, is there a component that would allow me to collapse/expand X task in the grid?)

1. (X1,X2 is sub task of X.  ie. when all sub tasks completed, the parent task is done).
2. sorted in task deadline order.
3. Once task is completed/ignored, it doesn't show in view.

can you pls advise on appropriate sql code for view as above.  I imagine it would have mix of:
1.  Some type of joins between 3 tables.
2.  Recursive self joins in date-type table, for parent-child tasks to n layer.
3.  Conditions for only task dates that are not 'complete or ignored'.
4.  Conditions for meeting dates that are in the future.
5.  sorting in task order.

Hope that makes sense.

« Last Edit: February 08, 2020, 11:43:18 am by lawman »


TinyPortal © 2005-2018