SQL join question

[Bob Sneidar]

Hi Pete. 

I can post the code that sqlYoga needs for this. I suspect if I wrote out the actual SQL and ran it in a utility, it would work as expected. 

      tableobj_createObject "devices"
      tableobj_createObject "accessories"
      tblrelation_createObject "devices|accessories"
      tblrelation_set "devices|accessories", "type", "one-to-many"
      tblrelation_set "devices|accessories", "left table", "devices"
      tblrelation_set "devices|accessories", "left table key", "deviceid"
      tblrelation_set "devices|accessories", "right table", "accessories"
      tblrelation_set "devices|accessories", "right table key", "deviceid"
      put sqlquery_createObject("devices") into theDeviceObject
      sqlquery_set theDeviceObject, "related table joins", “LEFT JOIN accessories"
      sqlquery_retrieveAsArray theDeviceObject, aDeviceData

While this is not SQL, you can see that the left table is devices and the right table is accessories. I will shoot off an email to BlueMangoGroup and see what they say. 

Bob S


> On May 15, 2015, at 11:07 , Peter Haworth <pete at lcsql.com> wrote:
> 
> Hi Bob,
> Something not right there.  A LEFT JOIN should return all entries from the
> left table, presumably devices in your example, whether or not there are
> any matching entries in the other side of the join (accessories).
> 
> One possibility is that your SELECT statement should name the devices table
> in the FROM clause and the JOIN should name the accessories table.  if the
> SELECT names the accessories table and the JOIN goes to the devices table,
> that would give the result you're seeing.
> 
> Hard to diagnose further without seeing your SELECT statement.
> 
> Pete