I want to combine data that is on the other side of a join table and I’m struggling to get the syntax right. Would someone provide me with the basic syntax?
When you deal with two tables, you use a JOIN statement where a field on both side has an equal value.
With a join table then you deal with three tables: the Left Table, the Right Table and the Middle Table. You then need two JOIN statements, one between the Left Table and the Middle Table and another between the Middle Table and the Right Table.
One suggestion: provide the fields in you Left and Right Table, with the key fields and we will be able to provide an example.
All sorted, thanks @planteg.
The syntax below works. The error that I couldn’t get past was that I was saying “From contacts as c, email_addresses as e, email_addr_rel as rel” then using the aliases in the join instructions.
SELECT c.last_name, c.first_name, c.email_address
FROM contacts as c
JOIN email_addr_rel as rel ON c.id = rel.contact_id
JOIN email_addresses as e ON rel.email_address_id = e.id
where c.primary_address_country = “Japan”
Malcolm, we try to keep the SQL as simple as possible due to the performance hit when a JOIN is added. We do everything to avoid using JOIN.
In your example above, the following suggestions wouldn’t work, but I’m afraid for something like this we’d throw the rules of normalisation out of the window and store a default email address in the contact table from the email addresses table, not only for the address list example you’ve provided, but for instance when we provide ‘type ahead find’ lists for example, where cross table finds can be very slow in comparison to local finds.
The 2 main avoid JOIN techniques we use (not applicable to your example above) are, if possible, to use a FileMaker relationship foreign field in the ExecuteSQL variable(s) and use ‘-=?’ in the SQL. This way, we can run the SQL on the desired table but base the WHERE value on the related table.
The other is to use IN as much as possible, where it is much faster to run 2 SQL SELECTs, the first to create a subset that the second then refers to, which is far, far quicker than using a JOIN.
Again, I reiterate, neither of these would be appropriate, that I can see with the time I have available, to your requirement above, but we’d always look for a way round having to use JOIN.
2 Likes
Hi Andy,
thanks for the advice. It’s good to be reminded of those things.
In this instance we don’t have to worry about those issue. We have an SQL data source and we’re creating CSV.
Malcolm
1 Like
Hi !
The “as” works but is unnecessary. You could also write :
[…]
FROM contacts c
JOIN email_addr_rel rel
[…]
etc…
Bye, Fred
4 Likes
Oh and if you did not have it or just for future visitor, here is the official PDF for FQL syntax :
FileMaker®16 SQL Reference
1 Like
Hi Fred
Welcome, good to see you here, I had thought the same.
All the best
Andy
1 Like
Welcome Fred.
The Swiss gang together again 
1 Like
welcome, Fred!!
1 Like
Hi Fred,
Thanks for the extra information. I like using AS because it clarifies the code for me, and without it, I often overlook the alias.
Malcolm
1 Like
Sometimes you can improve performance of join by placing all query expressions from the WHERE-clause within the JOIN predicate. It looks redundant but can speed things up tremendously (at least as experienced in FM16).
3 Likes
One other thing we are doing more and more now, particularly in systems where the source product is complex, such as insurance plans or ERP systems where product pricing can depend on many differnt suppliers, quantities, prices, dates, lead times etc.
Rather than carry out the calculation when the user is entering the data, we generate the various options available whenever the source product is created or amended and store this in a field within the product record. This is sometimes stored as JSON or CSV as appropriate.
Now this is once again throwing normalisation in the bin, but all the difficult data consolidation is taking place once during product maintenance, which happens relatively rarely.
In the past, say for an insurance product, at line item level we’d have had either multiple relationship links or use complex SQL dependent on data ranges, age, product type, etc.
Now we can either have a single relationship to the product or use a simple SELECT within ExecuteSQL and pull the consolidated information including all options from this single field into a variable and extract the appropriate data based on the parameters being entered.
We’re offsetting the additional information being stored at source by the fact it is only generated once and not every time users are entering data. This reduces the load on the LAN/WAN and the server and has resulted in much quicker data processing for all users.
2 Likes
Andy great points! We use caching a lot to and look-ahead logic as you describe by pregenerating data structures to ease the ExecuteSQL queries. By caching we keep a lot temp data in variables to reduce JOIN complexity if applicable.
Does your technique benefit from assigning more RAM memory to FMP in application settings?
1 Like
That’s a really good question FileKraft.
There is absolutely no doubt a moderate increase to the standard 128Kb cache in FMP(A) will improve performance. Whether this is related to our ‘caching’ of data is unclear.
We have a delicate path to tread as all copies of FileMaker are streamed and the more we increase the cache, the more RAM per remote desktop session consumes and the more RAM we have to allocate to each RemoteApp server and the more it costs us.
It is difficult enough balancing the RAM per server, as users that use multiple windows consume more RAM than those that don’t and RAM usage just increments and never goes down until a user logs out.
My only honest answer is ‘we don’t know’
2 Likes
thanks Andy - maybe another thread to discuss here - depending on file size - RAM size even HD/SSD config. - interesting is though that FMP allows memory assignment of >2GB in FMPA18 …