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
>
>
