MYSQL Distinct & Limiting Results

[Borderline]

Hi

I'm trying to create a page for a stallion showing his offspring. I used the distinct query in the hope of showing each of his children only once, but I'm assuming that because the other data in the row differs (there is a unique reference number called runid) the foals are showing multiple times, as shown here: http://www.arabianraceform.co.uk/breeding/sires/00001.php

Below are PDF versions of the data in each table (just the data that applies to thus stallion):

Horse table: http://www.arabianraceform.co.uk/breeding/sires/2014-02-23_horses.pdf
Runners table: http://www.arabianraceform.co.uk/breeding/sires/2014-02-23_horses.pdf

I'm looking to produce a scenario where each foal is listed just once, preferably using their last run in the runners table as the record. Any advice on how I could adapt my current code (pasted below) to produce this result would be much appreciated.

<?php $result = mysqli_query($con,   "SELECT DISTINCT    horse.horse, horse.h_link, horse.h_ctry, horse.colour, horse.gender, 
                                                        horse.dam, horse.s_link, horse.d_link, horse.d_ctry,
                                                        runners.raceid, runners.horse, 
                                                        SUBSTRING(runners.raceid,1,4) AS raceyear
                                                        FROM                horse 
                                                        INNER JOIN      runners
                                                        ON                  horse.horse = runners.horse
                                                        WHERE               horse.s_link = '00002.php'
                                                        ORDER BY            horse.horse"); 

                                      include($_SERVER['DOCUMENT_ROOT'].'/inc/tables/breeding/sires.php');?>

[diafol]

A bit confusing. Not sure how your tables are constructed. Here's my take - but there may be a better way...

Create separate, related tables for:

• horses [horse_id,name,colour_id,gender_id,a2z_id,country_id,sire_id, dam_id]
• colours [colour_id, colour, abbrev]
• genders [gender_id, gender, abbrev]
• a2z [a2z_id, a2z]
• countries [country_id, country, abbrev]
• races [race_id, venue_id, dateandtime, conditions_id, num_runners, distance, track_type_id]
• results [result_id, race_id, horse_id, position, code_id]
• venues [venue_id, name, country_id]
• conditions [condition_id, condition, abbrev]
• codes [code_id, plaintext, abbrev (e.g. DQF, Fell, DNF)]
• track_type [track_type_id, track_type, abbrev]

Note related fields (foreign keys) in bold type.

The sire_id and dam_id are "self-related". Although this can be awkward, it's better than creating a separate table for progeny as this pattern would soon run into trouble.

With this structure you could do something like...

SELECT CONCAT(LPAD(h.horse_id,4,"0"),".php") AS link, h.name, c.colour, g.gender, a.a2z, co.country, h2.name AS dam
FROM horses AS h 
    LEFT JOIN colours AS c ON c.colour_id = h.colour_id 
    LEFT JOIN genders AS g ON g.gender_id = h.gender_id 
    LEFT JOIN a2z AS a ON a.a2z_id = h.a2z_id
    LEFT JOIN countries AS co ON co.country_id = h.country_id
    LEFT JOIN horses AS h2 ON h2.horse_id = h.dam_id
WHERE h.sire_id = 1

To get the progeny from horse no.1
There is no "page link" field in the table - but it can be created on the fly from the horse_id for your purposes - or you could use php to generate the link.

[rch1231]

Without digging into the sql you have so far and based on what you are looking to do I suggest you look into using Group By and then if still needed the HAVING option to limit the output after it has been selected.

[Borderline]

Many thanks for your assistance - group by worked perfectly, but in the long term I want to look into implementing your suggestion diafol. Initially I had the data all split down into different tables but with the joins the pages became very slow to load.

[diafol]

Joins can be a pain! However you could avoid loads of joins with creating a progeny table so records aren't self-relating:

Progeny: prog_id | sire_id | dam_id

All of which are FKs from the horse_id in the horses table. As noted the group/having will work for small data sets, but data duplication such as storing sire names instead of sire ids etc will inevitably lead to problems. I suggest that you try to produce related tables sooner rather than later as trying to rejig when you have masses of data will break your heart. ;)

[Borderline]

I really do appreciate your help; have had some minor rejigs already and can fully see your point!