开发者

group_concat missing on of the group

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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜