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)
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.