Joining DB tables using the SQL Yoga Library

This is the place to post technical queries about SQL Yoga

Moderators: heatherlaine, Klaus, FourthWorld, robinmiller, trevordevore

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

Joining DB tables using the SQL Yoga Library

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

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

Hi Trevor:

I am new to databases and I have been using the SQL Yoga library (love it). So far it's been great.

I am trying to join two tables and have run into an impasse as to how to accomplish the join using the SQL library.
The two table names are CYTO2010Data and AP2010Data

I am using the Valentina DB with a Runtime Rev front end.

In Valentina the SQL join is accomplished by -

Code: Select all

Select CYTO2010Data.Accession,CYTO2010Data.PathologistDX,AP2010Data.Accession,AP2010Data.Code from CYTO2010Data, AP2010Data where CYTO2010Data.MRN= AP2010Data.MRNM AND CYTO2010Data.PathologistDX like '%GYB HSIL%' and AP2010Data.Accession not like '% C-%' and AP2010Data.Accession not like '%CC%' and AP2010Data.Code like '%Y%'
I am not sure how to accomplish the above join and retrieve the data for display using the SQL Yoga library.
Trevor DeVore
ScreenSteps - http://www.screensteps.com

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

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

Re: Joining DB tables using the SQL Yoga Library

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

Hi Jorge,

Take a look at the table relationships chapter in the manual:

http://revolution.screenstepslive.com/s ... pters/2064

SQL Yoga can automatically generate the JOIN clause of the query for you if you define a table object. If you don't want to bother with table objects then you can set the "related table joins" property of a SQL Query object by hand. The following lesson shows examples:

http://revolution.screenstepslive.com/s ... g-Queries-

In your case you are using the WHERE clause to specify the JOIN conditions for the AP2010Data table. Here is some untested SQL Yoga code. It assumes you have created table objects and the necessary relationship objects. If you don't then you need to add the join condition to the "related table joins" property.

Code: Select all

put sqlquery_createObject("CYTO2010Data") into theQueryA 
sqlquery_set theQueryA, "related table joins", "AP2010Data" 
sqlquery_set theQueryA, "conditions", "CYTO2010Data.PathologistDX contains ':1' and AP2010Data.Accession does not contain ':2' and AP2010Data.Accession does not contain ':3' and AP2010Data.Code contains ':4'", "GYB HSIL", " C-", "CC", "Y"
After reading the above chapter let me know if you need clarification on anything.
Trevor DeVore
ScreenSteps - http://www.screensteps.com

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

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

Re: Joining DB tables using the SQL Yoga Library

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

Created relationship tables using the instructional info provided and
used sqlquery_set theQueryA, "related table joins", "RIGHT OUTER JOIN
AP2010Data".
Worked great. Thanks for the prompt response.
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”