Limiting fields returned by query objects

This is the place to post technical queries about SQL Yoga

Moderators: FourthWorld, heatherlaine, Klaus, robinmiller, trevordevore

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

Limiting fields returned by query objects

Post by trevordevore » Fri Oct 11, 2013 2:15 pm

[This post is an archive from the old SQL Yoga forums]

Given the following three tables:
  • tblFirstNames (firstname_key, firstname) -> (1, Bob), (2, Jim), (3, Bill)
  • tblSurnames (surname_key, surname) -> (1, Smith), (2, Johnson), (3, Jones)
  • tblPeople (people_key, firstname_key, surname_key) -> (1, 1, 2), (2, 3, 1), (3, 3, 3)
and Yoga relationship definitions that tie all of them together, is there a way to format the query so that it doesn't return the key values from all of the tables?

put sqlquery_createobject("tblPeople") into theQueryA
sqlquery_set theQueryA, "related table joins", "LEFT JOIN tblFirstNames, LEFT JOIN tblSurnames"

For example, right now the records get returned like this:

1, 1, 2, 1, Bob, 2, Johnson

2, 3, 1, 2, Jim, 1, Smith

3, 3, 3, 3, Bill, 3, Jones

and it would be better if it was like this:

1, Bob, Johnson

2, Jim, Smith

3, Bill, Jones

Or do I need to handle that clean up when I put the data wherever I'm going to put it?

After struggling to get my head around how Yoga works (particularly all the groundwork that needs to be laid; still not clear on scopes), it is definitely very cool, but I wonder if I'd be better off just knocking out the SQL manually. Dunno, have to give that one a think.
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

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

Re: Limiting fields returned by query objects

Post by trevordevore » Fri Oct 11, 2013 2:15 pm

You can specify which fields are returned in the query by setting the "select clause" property.

Code: Select all

sqlquery_set theQueryA, "select clause", "firstname_key, firstname, surname"
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

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

Re: Limiting fields returned by query objects

Post by trevordevore » Fri Oct 11, 2013 2:15 pm

Ah. Thanks!
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

Post Reply

Return to “SQL Yoga”