I have two tables, the first puts puts pairs of people into a group with fldPairNum and a second table which collects scores for each individual person.
The problem I have is that if only one of the pair has submitted a score, then only their name appears in the 'nameOfPair' column, but I really need both names. What can I do to fix this?
SELECT
group_concat(DISTINCT `delegate`.`fldFirstName`,' ',`delegate`.`fldSurname` SEPARATOR ' and ') AS 'nameOfPair',
Sum(`data`.`fldScore`) AS 'totalScore'
FROM
`data`
Inner Join `delegate` ON `data`.`fldDelegateID` = `delegate`.`fldID`
WHERE
`开发者_运维技巧delegate`.`fldCategory` > '0'
AND
`delegate`.`fldPairNum` > '0'
GROUP BY
`delegate`.`fldPairNum`
Many thanks Dave
SELECT GROUP_CONCAT(DISTINCT
`delegate`.`fldFirstName`, ' ', `delegate`.`fldSurname`
SEPARATOR
' and ') AS 'nameOfPair',
SUM(`data`.`fldScore`) AS 'totalScore'
FROM `delegate`
LEFT JOIN `data`
ON `data`.`fldDelegateID` = `delegate`.`fldID`
WHERE `delegate`.`fldCategory` > '0'
AND `delegate`.`fldPairNum` > '0'
GROUP BY `delegate`.`fldPairNum`
精彩评论