Thanks Nigel and Peter, I went for Nigel's solution below. Both very
useful, learnt a lot, thank you.
Cheers,
Nigel
nigel wood wrote:
> > Is there a way to total counts done in subqueries?
>
> Never done this but my educated guess is:
>
> SELECT
> `Notes`.`note_id`,
> `Notes`.`last_updated_datetime`,
> `Notes`.`event_date`,
> `Notes`.`subject`,
> `Notes`.`summary`,
> `Notes`.`content`,
>
> @linked_issues_count := (SELECT COUNT(*) FROM `Notes__Issues` WHERE
> `Notes`.`note_id` = `Notes__Issues`.`note_id` )
> AS 'linked_issues_count',
>
> @linked_people_count := (SELECT COUNT(*) FROM `Notes__People` WHERE
> `Notes`.`note_id` = `Notes__People`.`note_id` )
> AS 'linked_people_count',
>
> @linked_organisations_count :=( SELECT COUNT(*) FROM
> `Notes__Organisations` WHERE `Notes`.`note_id` =
> `Notes__Organisations`.`note_id` )
> AS 'linked_organisations_count',
>
> (@linked_issues_count + @linked_people_count + @linked_organisations_count)
> AS 'total'
>
> FROM `Notes`
> WHERE ( `added_user_id` = '6' )
> ORDER BY last_updated_datetime DESC
> LIMIT 25
>
> hope that helps
>
> Nigel
>
>
