how do I express a selfjoin in SQL using Filemakers 12 internal SQL syntax?
eg. table called 'school' with a selfjoin matching 'names' and not matching (unequal) 'ID'
Thanks in advance,
Further fine tuning: These two queries give identical and expected results for a self join
SELECT B.pk FROM school A JOIN school B ON A.Name=B.Name WHERE A.pk<>B.pk
SELECT B.pk FROM school A, school B WHERE A.Name=B.Name AND A.pk<>B.pk
I wonder if there are any SQL cracks among us who can tell what's the difference, if there is any.
Edit: in fact, the statements above return all possible matches independent of the currently selected record, but we can modify the statement using an additional argument (school::pk) like below and it works as if we would look through a portal:
ExecuteSQL ( "Select .pk FROM school A JOIN school B ON A.Name=B.Name WHERE A.pk<>B.pk AND A.pk=?";"";"";school::pk)
To go even further it is possible to use a FM variable with the ExecuteSQL command e.g.
ExecuteSQL ( "Select .pk FROM school A JOIN school B ON A."& field &"=B."& field &" WHERE A.pk<>B.pk AND A.pk=?";"";"";school::pk)
You can find an example of the needed SQL for a self join in the ODBC JDBC guide. (I've confirmed with someone at Filemaker the the select statement syntax documented here also applies to Execute SQL.)
If I've decoded it correctly, You can use
FROM School A Join School B WHERE A.Name = B.Name AND A.ID <> B.ID
thanks for your comment, but I wasn't able to get any results with your proposal of using the JOIN
In the FM ODBC documentation, I found this example for a self join, without using JOIN which only has one condition
SELECT * FROM employee E, employee F WHERE E.manager_id = F.employee_id
Also using the query as explained here, doesnt work: http://www.w3resource.com/sql/joins/perform-a-self-join.php
SELECT * FROM company a, company b WHERE a.company_city=b.company_city AND a.company_name<>b.company_name
I only get results for leaving out the unequal condition A.ID<>B.ID, like that:
SELECT * FROM School A, School B WHERE A.Name = B.Name
But of course that's not what I'm looking for.
It might be, that there is a problem with the unequal condition ...
Turns out, that in my example I had the primary key named _pk and SQL doesn't like the leading underscore.
We are all fumbling our way through the use of this function based on very incomplete documentation. A quick check shows that the correct syntax for my example would have been:
FROM School A INNER JOIN School B ON A.Name = B.Name AND A.ID <> B.ID
As I look at your examples, I'm wondering if you really need any join here. Wouldn't this query work for you?
ExecuteSQL ( "Select * from School Where School.name = ? AND School.ID <> ?" ; Char ( 9 ) ; ¶ ; School::Name ; School::ID )
That pulls up tab delimitted fields with records separated by returns for all records in School that have the same value in Name but not the same ID as the current record.
And it does confirm that <> works as the "does not equal" operator where ≠ does not.
Retrieving data ...