AnsweredAssumed Answered

SQL JOIN Query

Question asked by piaccounting on Oct 29, 2017
Latest reply on Oct 30, 2017 by beverly

After lots of trial and error I’ve managed to get an SQL statement requiring a JOIN to work.  The FileMaker Training Series suggests there are two methods available; one using the JOIN clause and the other listing the tables.

 

Using the JOIN clause I cannot get the query to return the required data:

 

Let ( [ SQL = "SELECT AVL.ID FROM AVL

JOIN BAVL ON BVL.ID = BAVL.fkAssocID

JOIN BCVL ON BAVL.fkBusCatID = BCVL.ID

WHERE BCVL.Category = ?" ] ;

ExecuteSQL ( SQL ; "" ; ""; "Member"))
// Let

 

But with the alternative method I can:

Let ( [ SQL = "SELECT AVL.ID FROM AVL, BAVL, BCVL

WHERE AVL.ID = BAVL.fkAssocID and BAVL.fkBusCatID = BCVL.ID
and BCVL.Category = ? " ] ;

ExecuteSQL ( SQL ; "" ; "" ; "Member")) //Let

 

Is anyone able to offer any reasons why this may be or if there is a flaw in the logic of the former statement, which returns a “?” In my actual script "Member" becomes a global variable but this was altered to test in the Watch window.

 

Many thanks for any guidance.

UPDATED statement resolved after comments received  from philmodjunk

Let ( [ SQL = "SELECT AVL.ID FROM AVL

JOIN BAVL ON AVL.ID = BAVL.fkAssocID

JOIN BCVL ON BAVL.fkBusCatID = BCVL.ID

WHERE BCVL.Category = ?" ] ;

ExecuteSQL ( SQL ; "" ; ""; "Member"))
// Let

Outcomes