Consulta sql

[andrea55]

Hola tengo una tabla con los atributos, IDSocio IDeporte IDClub

como hago para listar los socios que participaron en mas de 10 deportes y en mas de 5 clubes? Gracias

[quimfv]

No lo he usado nunca pero mira lo que dice el manual de Mysql

COUNT(DISTINCT expr,[expr...])

Returns a count of the number of different non-NULL values.

COUNT(DISTINCT) returns 0 if there were no matching rows.

mysql> SELECT COUNT(DISTINCT results) FROM student;
In MySQL, you can obtain the number of distinct expression combinations that do not contain NULL by giving a list of expressions. In standard SQL, you would have to do a concatenation of all expressions inside COUNT(DISTINCT ...).

Con SQL standar...???...

Código sql:

SELECT `Socio`, `Clubs`, `Deportes`
   FROM
      (SELECT `subClubs`.`IDSocio` AS `Socio`, COUNT(*) AS `Clubs` 
         FROM (SELECT DISTINCT `tuTabla`.`IDSocio`, `tuTabla`.`IDClub` AS `Club`
                        FROM `tuTabla`) AS `subClubs`
         GROUP BY `subClubs`.`IDSocio`
         HAVING COUNT(*)>5) AS SocioClub
   INNER JOIN
      (SELECT `subDeportes`.`IDSocio` AS `Socio`, COUNT(*) AS `Deportes`
          FROM (SELECT DISTINCT `tuTabla`.`IDSocio`, `tuTabla`.`IDDeporte` AS `Deporte`
                       FROM `tuTabla`) AS `subDeportes`
          GROUP BY `subDeportes`.`IDSocio`
          HAVING COUNT(*)>5) AS SocioDep
   ON  SocioClub.Socio=SocioDep.Socio;
 

Quim