I think I probably can't do what I want, but am hoping I'm wrong. Please
help :)
I have three tables:
Organisations
----------------------------
- organisation_id - name -
----------------------------
- 1 - Org A -
- 2 - Org B -
----------------------------
Notes__Organisations
-----------------------------
- organisation_id - note_id -
-----------------------------
- 1 - 10 -
- 1 - 11 -
-----------------------------
Organisations__People
-------------------------------
- organisation_id - person_id -
-------------------------------
- 1 - 20 -
- 1 - 21 -
- 1 - 22 -
- 1 - 23 -
- 2 - 24 -
-------------------------------
"Organisations" has one-to-many relationships with
"Notes__Organisations" and "Organisations__People".
I want to select the name and id from "Organisations" along with a count
of the number of one-to-many relationships it has in each of the two
tables. I want a count of 0 if there are none, so I'm using two LEFT
JOINs and a GROUP BY.
I want to do this in a single query (so I can limit the set and sort it,
displaying the limited set in my interface).
I have:
=========================================
SELECT
`Organisations`.`organisation_id`,
`Organisations`.`name`,
COUNT(`Notes__Organisations`.`organisation_id`) AS 'linked_notes_count',
COUNT(`Organisations__People`.`organisation_id`) AS 'linked_people_count'
FROM
`Organisations`
LEFT JOIN
`Notes__Organisations`
ON
`Organisations`.`organisation_id` = `Notes__Organisations`.`organisation_id`
LEFT JOIN
`Organisations__People`
ON
`Organisations`.`organisation_id` =
`Organisations__People`.`organisation_id`
GROUP BY
`Organisations`.`organisation_id`
ORDER BY
name
LIMIT 50, 25
=========================================
Obviously the LIMIT does not apply to this example data, but I wanted to
show my whole query.
So with the above data I want:
-----------------------------------------------------------------------
- organisation_id - name - linked_notes_count - linked_people_count -
-----------------------------------------------------------------------
- 1 - Org A - 2 - 4 -
- 2 - Org B - 0 - 1 -
-----------------------------------------------------------------------
What I actually get is unpredictable. Something like:
-----------------------------------------------------------------------
- organisation_id - name - linked_notes_count - linked_people_count -
-----------------------------------------------------------------------
- 1 - Org A - 4 - 4 -
- 2 - Org B - 0 - 1 -
-----------------------------------------------------------------------
But it varies and there's no pattern.
Is there a way? Should I use a stored procedure instead to do the
counts? (not used them yet - don't even know if that's a valid suggestion).
Thanks in advance,
Nigel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
