Using JOINS to build datagrid

This is the place to post technical queries about SQL Yoga

Moderators: heatherlaine, Klaus, FourthWorld, robinmiller, trevordevore

Post Reply
nrprioleau
Posts: 24
Joined: Mon May 09, 2016 10:53 am

Using JOINS to build datagrid

Post by nrprioleau » 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

nrprioleau
Posts: 24
Joined: Mon May 09, 2016 10:53 am

Re: Using JOINS to build datagrid

Post by nrprioleau » 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?

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 873
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: Using JOINS to build datagrid

Post by trevordevore » 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
Trevor DeVore
ScreenSteps - http://www.screensteps.com

Levure Application Framework: https://github.com/trevordevore/levure
LiveCode Resources for Developers: http://livecode.bluemangolearning.com

Post Reply

Return to “SQL Yoga”