Here is the snapshot of the query that doesn't work since I added the Union.

SELECT fin05_usager.idUsager,
       (SELECT sum(nombreReputation) as nombreReputation
          FROM (SELECT SUM(nombreReputationGagner) as nombreReputation
                 FROM fin05_usager_reputation 
                WHERE fin05_usager_reputation.idUsager = fin05_usager.idUsager
             GROUP BY fin05_usager_reputation.idUsager
        UNION
                SELECT SUM(cc_badge.valeurEnReputation) as nombreReputation
                  FROM c开发者_运维百科c_badge, fin05_usager_badge
                 WHERE fin05_usager_badge.idBadge = cc_badge.idBadge
                   AND fin05_usager_badge.idUsager = fin05_usager.idUsager) as repuUnion    
       ) as repu
 FROM fin05_usager
WHERE fin05_usager.idUsager = 6

The error is : #1054 - Unknown column 'fin05_usager.idUsager' in 'where clause'

If I remove the fin05_usager.idUsager and use directly '6' it does work.

If I remove the union and use only one of the 2 select it works (what ever if I take the FROM fin05_usager_reputation or the other one FROM cc_badge, fin05_usager_badge.

Why when using the UNION the error about finding the idUsager appear and without the union no error is found?

Schema simplified:

fin05_usager: idUsager int(8)

fin05_usager_reputation : idUsager int(8), nombreReputationGagner int(4)

cc_badge : idBadge int(4), valeurEnReputation int(4)

fin05_usager_badge : idUsager int(8), idBadge int(4)

Note:

I cannot do the subquery directly in the query. I have to use it inside a subquery in the select because in real, the query is very big and already contain Group, etc.

When you mention that removing the UNION causes things to work, I'm betting that you're removing this specifically:

UNION
SELECT SUM(cc_badge.valeurEnReputation) as nombreReputation
  FROM cc_badge, fin05_usager_badge
 WHERE fin05_usager_badge.idBadge = cc_badge.idBadge
   AND fin05_usager_badge.idUsager = fin05_usager.idUsager

This piece contains the refernce to fin05_usager.idusager that the mysql error is referring to. Subquery access is only allowed to one level - both ways. Nevermind that you really shouldn't be doing a SELECT within the SELECT clause. Here's how I reinterpret your query:

SELECT fu.idUsager,
       a.nombreReputation + b.nombreReputation AS repuunion
  FROM fin05_usager fu
  JOIN (SELECT fur.idusager,
               SUM(fur.nombreReputationGagner) as nombreReputation
          FROM fin05_usager_reputation fur 
         WHERE fur.idUsager = fin05_usager.idUsager
      GROUP BY fur.idUsager) a ON a.idusager = fu.idusager
  JOIN (SELECT fub.idUsager,
               SUM(ccb.valeurEnReputation) as nombreReputation
          FROM cc_badge ccb
          JOIN fin05_usager_badge fub ON fub.idbadge = ccb.idbadge
      GROUP BY fub.idUsager) b ON b.idusager = fu.idusager
 WHERE fu.idUsager = 6

Alright,

The only way I found for the moment without doing a Group By in the main query (since in the real situation I can't because it does contain already a Group By clause) is to do it in 2 and to merge it with the code... not exaclty what I would like but it works at least:

    SELECT fin05_usager.idUsager,

                (SELECT sum(nombreReputationGagner) as nombreReputation1
                FROM fin05_usager_reputation 
                WHERE fin05_usager_reputation.idUsager = fin05_usager.idUsager
                group by fin05_usager_reputation.idUsager) as nombreReputation1
            ,
                (SELECT sum(cc_badge.valeurEnReputation) as nombreReputation2
                FROM cc_badge,  fin05_usager_badge
                WHERE fin05_usager_badge.idBadge = cc_badge.idBadge
                AND fin05_usager_badge.idUsager = fin05_usager.idUsager) as nombreReputation2

    FROM 
        fin05_usager
        where fin05_usager.idUsager = 6

In the code I sum the nombreReputation1 and nombreReputation2.