[TYPO3-dev] sql order by with left join

[Bas van der Togt]

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
> 
> 
> 
> 
> 
>