[TYPO3-dev] sql order by with left join

[Bas van der Togt]

Bas van der Togt wrote:
> Thank you for your answer Ries!
> I tried both ways to fix the problem but without any result.
> The mm table is filled with valid data:
> 
> uid_local    uid_foreign    tablenames    sorting
> 1        1                3
> 1        2                2
> 1        3                1
> 2        1                1
> 2        2                2
> 2        3                3
> 
> Any other ideas?
> 
> kind regards,
> Bas   
> 
> ries van Twisk wrote:
>> hey Bas,
>>
>> are you sure that the sorting field in the table 
>> tx_profalsosee_cat_mm  contains valid data?
>>
>> I can hardly imagine that MySQL has a bug in that area.
>>
>> If you do suspect it's a bug try this:
>>
>> a) remove the LEFT from your joins and see if sorting get's corrected.
>> b) Setup your SELECT clause like this : SELECT mm.*, alsosee.*, 
>> cat.*,  alsosee.uid AS alsoseeuid
>>
>> But I don't think it will change anything...
>>
>> ries
>>
>> On Nov 5, 2007, at 9:28 AM, Bas van der Togt wrote:
>>
>>> Hello list,
>>>
>>> I'm trying to order a list in my extension but i can't get it work.
>>> The sql has a 'left join' and that must me the problem i think. Does
>>> anybody knows how to order my sql with a left join?
>>>
>>> SQL:
>>> SELECT alsosee.*, cat.*, mm.*, alsosee.uid AS alsoseeuid
>>> FROM tx_profalsosee_cat_mm AS mm
>>> LEFT JOIN tx_profalsosee_cat AS cat ON mm.uid_foreign=cat.uid
>>> LEFT JOIN tx_profalsosee_alsosee AS alsosee ON mm.uid_local=cat.uid
>>> WHERE alsosee.hidden=0 AND alsosee.deleted=0 AND alsosee.t3ver_state=0
>>> AND alsosee.pid IN ('.$pidList.')
>>> ORDER BY mm.sorting
>>>
>>> Kind regards,
>>> Bas
>>
>>
>>
>>
>>
>>

comment:
whole 'tablenames' column is empty so not the 'sorting' column!