Two separate relationships

[sal88]

Hi all

I could really do with a push in the right direction here, being still very new to SQL (i'm not sure if the title is accurate).

I have a Labour table, which has related data in an items table. Each item is also related to a line in the stock table.

So it's a one to many for Labour==Items and one to many for Stock==Items

I would like an ExecuteSQL calculation which will tell me if all the Items related to a Labour record meet the criteria of their related record in the stock table having the required quantity.

The required quantity is defined as being equal to or above the quantity of the Items record. If the stock record's 'unlimited' field is set to 1, then it has the required quantity regardless.

Any help really appreciated.

Thanks

 

[eos]

Say you have the relationships

Labour::id = Item::id_labour, and Item::id_stock = Stock::id;

and the fields

Item::qty and Stock::qty, and a Boolean (numeric) field Stock::isUnlimited that is 1 if unlimited, and empty if not,

then try

Let ( [ 
  countAll = 
    ExecuteSQL ( " 
      SELECT COUNT (*)
      FROM Item
      WHERE id_labour = ?
      " ; "" ; "" ; Labour::id
    ) ;
   countQualifying = 
    ExecuteSQL ( " 
      SELECT COUNT (*)
      FROM Item I
      JOIN Stock S On S.id = I.id_stock
      WHERE 
        id_labour = ? AND 
        ( I.qty <= S.qty OR S.isUnlimited IS NOT NULL ) 
      " ; "" ; "" ; Labour::id
    )
  ] ;
  countAll = countQualifying
)

Edited August 7, 2015 by eos

[sal88]

Oh my! That's beautiful! Thank you eos, yet again you have shown me the way with your skills!

Question: how does the question mark work with the arguments parameter? What would you do if you had multiple arguments (if that's possible?)?

Also, the use of the letters 'I' and 'S' - could these have been any letters? Are they only used when defining a JOIN?

 

Edited August 7, 2015 by sal88

[eos]

Question: how does the question mark work with the arguments parameter?

Substitution by position. Add more ?s and more arguments, and make sure their positions match your intentions:

ExecuteSQL ( "
  SELECT reference
  FROM Documentation
  WHERE funcName = ? AND version = ?
  "; "" ; "" ; "ExecuteSQL" ; 13
)

But allow me to refer you to http://fmhelp.filemaker.com/fmphelp_13/en/html/func_ref3.33.6.html#1062707 

Also, the use of the letters 'I' and 'S' - could these have been any letters?

More or less any (try 'Ü' as alias…); it could also be several letters, but from a certain point you would have to consider checking on SQL reserved words. I.e., 

ExecuteSQL ( "
  SELECT CASE.intro
  FROM CaseScenarios CASE
  JOIN SomeOtherTable S ON CASE.id = …

will return an error. If in doubt, you can also use a fully qualified reference: SELECT CaseScenarios.intro FROM CaseScenarios

though this is really only necessary on JOINs / sub-queries or when you reference same-named fields from different tables (or the same field from different instances of the same table).

Are they only used when defining a JOIN?

In “real“ SQL engines, they are used in other places, too – but AFAICT, that syntax either doesn't apply or doesn't work in ExecuteSQL() (with the exception of sub-queries).

I suggest you get hold of Beverly Voth's handy ExecuteSQL reference PDF where she discusses these and other points in more detail.

And as always: experiment!

 

[beverly]

hello, sal88! eos has given wonderful advice! Let me add more.  

Oh my! That's beautiful! Thank you eos, yet again you have shown me the way with your skills!

Question: how does the question mark work with the arguments parameter? What would you do if you had multiple arguments (if that's possible?)?

Also, the use of the letters 'I' and 'S' - could these have been any letters? Are they only used when defining a JOIN?

 

The 'I' and 'S' are called table alias'. They are ONLY *required* if you have more than one field/column with the same name in more than one table. They are *helpful* is you have an unusually long table name and do not want to retype it several times. They are *helpful* if you otherwise need to quote the table name (escaped quote), because you have used a reserved word or other unusual characters.  Let me give you an example:

table1 has the columns 'name', 'id', 'phone'

table2 has the columns 'full name', 'id', 'cell'

there are fields/columns the SAME in the two tables, so the JOIN uses the alias helps SQL know which is associated with the correct column. (the "AS" in the alias declaration  is optional)

" SELECT \"full name\", t2.\"id\", \"cell\"

FROM \"table with spaces\" AS t2

JOIN table1 AS t1 

ON t2.\"id\" = t1.\"id\" "

this is very clear to SQL that 'id' is two unique columns (two different tables). NOTE that the alias (or table  name) is NOT required to be used at all except in this case.

May I refer you to another document? fm executeSQL reference (this says "12", but is valid for 12, 13 and 14). Be sure to get the PDF and example file(s) as the blog article is not as complete. The Japanese version of the pdf is here: http://notonlyfilemaker.com/wp-content/uploads/2014/01/The-Missing-FM12-ExecuteSQL-Reference-ja.pdf

beverly

 

 

[sal88]

Thank you Beverly and eos! You've been so helpful, it makes sense to me now. I am finally using SQL in my FM solution! It is such a time saver, I can't believe I went so long without it.

Here's to SQL!