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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
