LiveCode Forums

Posted: **Mon May 09, 2016 10:58 am**

Good morning,

Is it possible to use joins to build columns in a datagrid? I have a "sales" datagrid that has a column productID, I want the datagrid to display the name. My code looks like this:

Code: Select all

put sqlquery_createObject("sales") into theQueryA
   sqlquery_set theQueryA, "related table joins", "LEFT OUTER JOIN customers, LEFT OUTER JOIN products"
   sqlquery_set theQueryA, "order by", "sales.sales_ID"

   sqlquery_retrieveAsRecords theQueryA, theDataA
   put the result into theError
   
   if theError is empty then
      set the dgData of group "salesDataGrid" to theDataA
   end if
   
   if theError is not empty then
      answer "Error populating customers:" && theError & "."
   end if

Columns in my datagrid for products would be products.prod_name for example. The products information is in the returned array but I don't know how to reference it.
Please help

Posted: **Mon May 09, 2016 11:56 am**

I have figured it out. I used retrieveAsArray instead of retrieveAsRecord.

However, now that I have all my data, I have noticed that there are two addresses related to the customer record but the returned data is only returning one address record. What am I doing wrong?

Posted: **Tue May 10, 2016 1:41 pm**

Can you show me the query being generated by SQL Yoga? Just use

Code: Select all

set the clipboardData to sqlquery_get(theQueryA, "query")

.

Also, can you show me a small sample of what the

Code: Select all

theDataA

array looks like? Here is a function that will print out the contents of an array.

Code: Select all

function PrintArray @pArray, pDimension, pFullData
   if pDimension is empty then put 0 into pDimension
   
   put the keys of pArray into theKeys
   sort theKeys numeric
   
   repeat for each line theKey in theKeys
      if pArray[theKey] is an array then
         put _printCharXTimes(space, pDimension * 5) & theKey & cr after theText
         put pArray[theKey] into theTempArray
         put PrintArray(theTempArray, pDimension + 1, pFullData) after theText
      else
         if pFullData then
            put _printCharXTimes(space, pDimension * 5) &  theKey & ":" && pArray[theKey] & cr after theText
         else
            put _printCharXTimes(space, pDimension * 5) &  theKey & ":" && line 1 of pArray[theKey] & cr after theText
         end if
      end if
   end repeat
   
   return theText
end PrintArray


private function _printCharXTimes pChar, pTimes
   local theStr
   
   repeat with i = 1 to pTimes
      put pChar after theStr
   end repeat
   return theStr
end _printCharXTimes

LiveCode Forums

Using JOINS to build datagrid

Using JOINS to build datagrid

Re: Using JOINS to build datagrid

Re: Using JOINS to build datagrid