MySQL Double JOIN with COUNT

[eluser]CoffeeBeanDesign[/eluser]
Hello team,

I have the following query :

Code:

SELECT `users`.`username`, `content`.*
FROM (`content`)
LEFT JOIN `users` ON `users`.`id`=`content`.`modified_by`
WHERE `content`.`status` != 0
AND `content`.`type` = 2
ORDER BY ABS(content.usort) ASC
LIMIT 10

I have another table assets2content which stores the relationships between assets and content like this :

id | asset_id | content_id

Presently, I am looping through each content result and doing a COUNT query on the assets2content table to see whether there are any assets related to it and if so how many.

Is it possible to do a double JOIN query with a COUNT - something along these lines :

SELECT (`users`.`username`, `content`.*, COUNT(`assets2content`.`content_id`) ...

Every time I try combinations of JOINS and other queries I either get no results or only one result - suggesting it's joining incorrectly on the singular COUNT result.

Your help would be greatly appreciated.

All the best.

[eluser]CoffeeBeanDesign[/eluser]
... solved it.

Code:

SELECT COUNT(assets2content.content_id) as assets, `users`.`username`, `content`.*
FROM (`content`)
LEFT JOIN `assets2content` ON `assets2content`.`content_id`=`content`.`id`
LEFT JOIN `users` ON `users`.`id`=`content`.`modified_by`
WHERE `content`.`status` != 0
AND `content`.`type` = 2
GROUP BY `content`.`id`
ORDER BY ABS(content.usort) ASC
LIMIT 10

It's that pesky GROUP BY function.

All the best.

[eluser]TheFuzzy0ne[/eluser]
Thanks for posting the solution to your problem. Please would you re-edit your posts and include your SQL code in [code][/code] tags? Thanks.

[eluser]CoffeeBeanDesign[/eluser]
[coded]

Thanks.