Using Joins, Group By and Sub Queries, Oh My!

I have a database with a table for details of ponies, another for details of contacts (owners and breeders), and then several other small tables for parameters (colours, counties, area codes, etc.). To give me a list of existing pony profiles, with their various details given, i use the following query:

SELECT *
    FROM profiles
        INNER JOIN prm_breedgender
            ON profiles.ProfileGenderID = prm_breedgender.BreedGenderID
        LEFT JOIN contacts
            ON profiles.ProfileOwnerID = contacts.ContactID
        INNER JOIN prm_breedcolour
            ON profiles.ProfileAdultColourID = prm_breedcolour.BreedColourID
    ORDER BY profiles.ProfileYearOfBirth ASC $limit

In the above sample, the 'profiles' table is my primary table (holding the Ponies info), 'contacts' is second in importance holding as it does the owner and breeder info. The lesser parameter tables can be identified by their prm_ prefix. The above query works fine, but i want to do more.

The first big issue is that I wish to GROUP the results by gender: Stallions, Mares, Geldings... I used << GROUP BY prm_breedgender.BreedGender >> or << GROUP BY ProfileBreedGenderID >> before my ORDER BY line, but than only returns two results from all my available profiles. I have read up on this, and apparantly need to reorganise my query to accomodate GROUP within my primary SELECT clause. How to do this however, gets me verrrrrrry confused. Step by step help here would be fantabulous.

As a further note on the above - You may have noticed the $limit var at the end of my query. This is for pagination, a feature I want to keep. I shouldn't think that's an issue however.

My secondary issue is more of an organisational one. You can see where I have pulled my Owner information from the contacts table here:

    LEFT JOIN contacts
        ON profiles.ProfileOwnerID = contacts.ContactID

I could add another stipulation:

        AND profiles.ProfileBreederID = contacts.ContactID

with the intention of being able to list a pony's Owner and Breeder, where info on either is available. I'm not sure how to echo out this info though, as $row['ContactName'] could apply in either the capacity of owner OR breeder.

Is this a case of simply running two queries rather than one? Assigning a variable $foo to the first run of the query, then just run another separate query altogether and assign $bar to those results? Or is there a smarter way of doing it all in t开发者_如何学Pythonhe one query (e.g. $row['ContactName']First-iteration, $row['ContactName']Second-iteration)? Advice here would be much appreciated.

And That's it! I've tried to be as clear as possible, and do really appreciate any help or advice at all you can give. Thanks in advance.

##########################################################################EDIT

My query currently stands as an amalgam of that provided by Cularis and Symcbean:

SELECT *
        FROM    (
            profiles
            INNER JOIN prm_breedgender
                ON profiles.ProfileGenderID = prm_breedgender.BreedGenderID
            LEFT JOIN contacts AS owners
                ON profiles.ProfileOwnerID = owners.ContactID
            INNER JOIN prm_breedcolour
                ON profiles.ProfileAdultColourID = prm_breedcolour.BreedColourID
            )
        LEFT JOIN contacts AS breeders
            ON profiles.ProfileBreederID = breeders.ContactID
        ORDER BY prm_breedgender.BreedGender ASC, profiles.ProfileYearOfBirth ASC $limit

It works insofar as the results are being arranged as I had hoped: i.e. by age and gender. However, I cannot seem to get the alias' to work in relation to the contacts queries (breeder and owner). No error is displayed, and neither are any Owners or Breeders. Any further clarification on this would be hugely appreciated.

P.s. I dropped the alias given to the final LEFT JOIN by Symcbean's example, as I could not get the resulting ORDER BY statement to work for me - my own fault, I'm certain. Nonetheless, it works now although this may be what is causing the issue with the contacts query.

---

GROUP in SQL terms means using aggregate functions over a group of entries. I guess what you want is order by gender:

ORDER BY prm_breedgender.BreedGender ASC, profiles.ProfileYearOfBirth ASC $limit

This will output all Stallions, etc. next to each other.

To also get the breeders contact, you need to join with the contacts table again, using an alias:

LEFT JOIN contacts AS owners
            ON profiles.ProfileOwnerID = owners.ContactID

LEFT JOIN contacts AS breeders
            ON profiles.ProfileBreederID = breeders.ContactID

---

To further expand on what @cularis stated, group by is for aggregations down to the lowest level of "grouping" criteria. For example, and I'm not doing per your specific tables, but you'll see the impact. Say you want to show a page grouped by Breed. Then, a user picks a breed and they can see all entries of that breed.

PonyID   ProfileGenderID    Breeder
1        1                  1
2        1                  1
3        2                  2
4        3                  3
5        1                  2
6        1                  3
7        2                  3

Assuming your Gender table is a lookup where ex:
BreedGenderID   Description
1               Stallion
2               Mare
3               Geldings

SELECT * FROM profiles INNER JOIN prm_breedgender ON profiles.ProfileGenderID = prm_breedgender.BreedGenderID

select
      BG.Description,
      count(*) as CountPerBreed
   from
      Profiles P
         join prm_BreedGender BG
            on p.ProfileGenderID = BG.BreedGenderID
   group by
      BG.Description
   order by 
      BG.Description

would result in something like (counts are only coincidentally sequential)

Description   CountPerBreed
Geldings      1
Mare          2
Stallion      4

change the "order by" clause to "order by CountsPerBreed Desc" (for descending) and you would get

Description   CountPerBreed
Stallion      4
Mare          2
Geldings      1

To expand, if you wanted the aggregations to be broken down per breeder... It is a best practice to group by all things that are NOT AGGREGATES (such as MIN(), MAX(), AVG(), COUNT(), SUM(), etc)

select
      BG.Description,
      BR.BreaderName,
      count(*) as CountPerBreed
   from
      Profiles P

         join prm_BreedGender BG
            on p.ProfileGenderID = BG.BreedGenderID

         join Breeders BR
            on p.Breeder = BR.BreaderID
   group by
      BG.Description,
      BR.BreaderName
   order by 
      BG.Description

would result in something like (counts are only coincidentally sequential)

Description   BreaderName  CountPerBreed
Geldings      Bill         1
Mare          John         1
Mare          Sally        1
Stallion      George       2
Stallion      Tom          1
Stallion      Wayne        1

As you can see, the more granularity you provide to the group by, the aggregation per that level is smaller.

Your join conditions otherwise are obviously understood from what you've provided. Hopefully this sample clearly provides what the querying process will do. Your group by does not have to be the same as the final order... its just common to see so someone looking at the results is not trying to guess how the data was organized.

In your sample, you had an order by the birth year. When doing an aggregation, you will never have the specific birth year of a single pony to so order by... UNLESS.... You included the YEAR( ProfileYearOfBirth ) as BirthYear as a column, and included that WITH your group by... Such as having 100 ponies 1 yr old and 37 at 2 yrs old of a given breed.

---

It would have been helpful if you'd provided details of the table structure and approximate numbers of rows. Also using '*' for a SELECT is a messy practice - and will cause you problems later (see below).

What version of MySQL is this?

apparantly need to reorganise my query to accomodate GROUP within my primary SELECT clause

Not necessarily since v4 (? IIRC), you could just wrap your query in a consolidating select (but move the limit into the outer select:

SELECT ProfileGenderID, COUNT(*)
FROM (
 [your query without the LIMIT]
) ilv
GROUP BY ProfileGenderID
LIMIT $limit;

(note you can't ORDER BY ilv.ProfileYearOfBirth since it is not a selected column / group by expression)

How many records/columns do you have in prm_breedgender? Is it just Stallions, Mares, Geldings...? Do you think this list is likely to change? Do you have ponies with multiple genders? I suspect that this domain would be better represented by an enum in the profiles table.

with the intention of being able to list a pony's Owner and Breeder,

Using the code you suggest, you'll only get returned instances where the owner and breeder are the same! You need to add a second instance of the contacts table with a different alias to get them all, e.g.

SELECT *
FROM (
  SELECT *
  FROM profiles
    INNER JOIN prm_breedgender
        ON profiles.ProfileGenderID = prm_breedgender.BreedGenderID
    LEFT JOIN contacts ownerContact
        ON profiles.ProfileOwnerID = ownerContact.ContactID
    INNER JOIN prm_breedcolour
        ON profiles.ProfileAdultColourID = prm_breedcolour.BreedColourID
) ilv LEFT JOIN contacts breederContact
     ON ilv.ProfileBreederID = breederContact.ContactID
ORDER BY ilv.ProfileYearOfBirth ASC $limit