<sigh> NVM. Of course, the query is going to rename the second siteid column for sites to siteid2.
Bob S > On Nov 28, 2022, at 16:11 , Bob Sneidar via use-livecode > <[email protected]> wrote: > > Hi all. > > If anyone has any experience in using sqlYoga for joins, I have a curious > issue. The following code produces a variable tFoundOrphans containing a list > of service record IDs with no corresponding siteid in the Sites table, so I > know the query object works. However, when I use sqlquery_delete with the > same object, I get an error! > > > sqlyoga_executesql_err,0,0,Unknown column 'sites.siteid' in 'where clause' > (DELETE FROM service WHERE sites.siteid IS NULL) > > Here's the code: > > put sqlquery_createObject("service") into qServiceObjectA > sqlquery_set qServiceObjectA, "select clause", "service.siteid, > sites.siteid" > sqlquery_set qServiceObjectA, "distinct", true > sqlquery_set qServiceObjectA, "joins", \ > "LEFT OUTER JOIN sites ON service.siteid=sites.siteid" > sqlquery_set qServiceObjectA, "conditions", "sites.siteid IS NULL" > put dbQuery(qServiceObjectA, "data") into tFoundOrphans > setStatusMsg "Deleting service orphans from the service table...", > tParentCard > > if tFoundOrphans is empty then > answer info "No orphaned service records found!" as sheet > else > sqlquery_delete qServiceObjectA > Answer info "Orphaned service records have been purged!" as sheet > end if > > The SQL I am shooting for is: > > SELECT DISTINCT > service.siteid > FROM > service > LEFT OUTER JOIN > sites > ON > service.siteid=sites.siteid > WHERE > sites.siteid IS NULL; > > This works also in a SQL editor. > > Bob S > > _______________________________________________ > use-livecode mailing list > [email protected] > Please visit this url to subscribe, unsubscribe and manage your subscription > preferences: > http://lists.runrev.com/mailman/listinfo/use-livecode _______________________________________________ use-livecode mailing list [email protected] Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
