I see why. sqlquery_delete does not take anything into account except for the
where clause. The join and distinct properties are ignored. I will have to
recode using some other method.
Bob S
> On Nov 28, 2022, at 16:30 , Bob Sneidar via use-livecode
> <use-livecode@lists.runrev.com> wrote:
>
> <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
_______________________________________________
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
