<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

Reply via email to