<sigh again> I don't think there is a way to do this with sqlYoga. I tried "...
service.siteid AS siteid1..." and then referring to the columns with their
aliases, but that still does not work.
Bob S
> On Nov 28, 2022, at 16:17 , Bob Sneidar via use-livecode
> <use-livecode@lists.runrev.com> wrote:
>
> <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
>> <use-livecode@lists.runrev.com> 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
>> use-livecode@lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your subscription
>> preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>
>
> _______________________________________________
> use-livecode mailing list
> use-livecode@lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription
> preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
_______________________________________________
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
