How about setting a variable to the md5hash then just using ExecuteSQL to query for that? Kind of like this:
Set Variable [$md5; Value: tableMD5::md5Hash] then do: ExecuteSQL( SELECT * FROM \"othMD5table\" WHERE \"otherMD5hash\" = ?"; ""; ""; $md5 )
The join syntax needs to reference two table occurrences by name both with the join and also in the "On" part of the clause
FROM Table1 Join Table2 On Table1.field = Table2.field
Can't tell from your post if this is the actual syntax that you are using and am not sure if you could set this up as Table1 join Table1 or not...
@PhilMidJunk Thanks, I am indeed trying to find dublicates within the same table, perhaps this is not possible with ExecuteSQL INNER JOIN
@ Chris Johnston, thanks, as output of the calculation I do need the whole list of ID's of records that have a duplicate, would that be possible by extending your suggestion?
Tell me are you try to end up with a found set or a list?
@Chris: A list.
FYI: When clicking a button 'find', I store the list of found ID's in a global (user interface table). The global (containing list of media asset id's) is connected to a media assets portal. My portal is the search result. The portal contents will only change when clicking search again (because that changes the id's in the global), this is intentional.
I do have a solution adding the ID's without ExecuteSQL, but since all my search criteria are using sql queries in the calculation, I'd prefer understanding how to also find duplicates in the same table with an ExcecuteSQL query.
Your SQL query will not produce a return separated list of values as written and I pointed out what appeared to be syntax errors in your query. Did you try correcting them?.
I suggest posting the full and detailed query and describing what results you now get. Do you still get that dreaded SQL error?