Nigel,
>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.
Aggregation multiplies across multiple joins. For suggested solutions
see "Aggregates across multiple joins" at
http://www.artfulsoftware.com/infotree/queries.php.
PB
-----
Nigel Peck wrote:
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
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.0.238 / Virus Database: 270.12.1/2071 - Release Date: 04/21/09 08:30:00