1 (edited by Paul 2019-01-31 16:23:33)

Topic: limitation with SQL JOINS in MVD?

Hello Dmitriy,
I am pretty new to all this, so please excuse me if this is a "non-issue question".

I was trying to tie a push button action to a SQL search. In the process,
while  trying to join 2 tables in a search, I got this error message from MVD:
"ERROR: RIGHT and FULL OUTER JOINs are not currently supported ".
(the SQL statement was actually calling for a LEFT OUTER JOIN).
Is that a limitation in the program, and if so, do you have any plans to add that functionality in an upgrade?
If it is a current limitation, is there a way around it?

Re: limitation with SQL JOINS in MVD?

Sqlite does not support Right and Full outer joins, you have to use workarounds see: https://www.sqlitetutorial.net/sqlite-full-outer-join/, using a MySQL database connection might support Right and Full outer joins.

Re: limitation with SQL JOINS in MVD?

http://myvisualdatabase.com/forum/viewtopic.php?id=4727

Re: limitation with SQL JOINS in MVD?

Thank you. The above 2 suggestions were helpful.

Re: limitation with SQL JOINS in MVD?

select
       a.id,
       a.Pump_Name,
       c.Grade_Name,
       sum(b.Delivery_Volume),   
       sum(b.Delivery_Value),
       Min(b.Hose_Meter_Volume)- b.Delivery_Volume,
       Max(b.Hose_Meter_Volume),
       d.Volume_Total_Current,
       (Max(b.Hose_Meter_Volume))- (Min(b.Hose_Meter_Volume)-b.Delivery_Volume),
       sum(b.Delivery_Volume)-(Max(b.Hose_Meter_Volume)-  (Min(b.Hose_Meter_Volume)-b.Delivery_Volume)),
       min(b.id)
       
from   tblPump a, tblhose d,tblGrade c,         
       tblHoseDelivery b
     
                   
where  a.id = b.id_tblPump and c.id = b.id_tblGrade and d.id = b.id_tblHose
  and  b.Completed_TS >= {FromTimePicker}
  and  b.Completed_TS <= {ToTimePicker}
  and  b.Del_Sell_Price > 20
   
group by a.id                     
;

hi.. see example to join many columns.. its easy query...