Posted by Stefan Rybacki on 10/14/01 11:30

dead@deadmail.com wrote:
> On Thu, 27 Oct 2005 01:07:22 +0100, in alt.php.sql you wrote:
>
>
>>On Thu, 27 Oct 2005 00:54:42 +0100, in alt.php.sql you wrote:
>>
>>
>>
>>>What I have is
>>>select t1.count(*) as count,t2.*, from t1,t2 order by count
>>>
>>>But that gives me an error pointing to (*)
>>>
>>>Can anyone give me the proper syntax plus explanation.. please:)
>>
>>
>>damm forgot the where
>>ID=x
>>
>>select t1.count(*) as count,t2.* from t1,t2 where t1.id=ID and
>>t2.id=ID order by count
>
>
>
> got it working nearly :(
>
> select count(t1) as count,t2.* from t1,t2 where t1.id=ID and
> t2.id=ID group by t1.id order by count
>
> BUT this only shows results from table 1 that also have soemhting in
> table two... I need to show all results of table one even if nothing
> in table two... i have tried replacing and with or ... that gives me
> all results.. but the count is wrong :(
>
> any help appreciated ? :)

Use a LEFT JOIN!
select count(t1) as count,t2.* from t1 LEFT JOIN t2 ON (t1.id=ID and
t2.id=ID) group by t1.id order by count

Regards
Stefan

[Back to original message]