Unknown column in subquery where clause

Your subquery does not have access to the tables in the outer query. That is, the membres table (aliased as M) is not available at the time that the couponsTypes subquery is evaluated.

However, such a subquery should not be necessary in this case; you merely need to join the tables directly and group the result:

SELECT   idMembre, MAX(coupons.idType) AS maxCouponType
FROM     membres JOIN coupons USING (idMembre)
GROUP BY idMembre
ORDER BY maxCouponType DESC

You are not allowed to reference outer tables in a subquery in a join clause. One way to solve this is by doing a group by in the subquery based on the join condition:

SELECT DISTINCT M.`idMembre`, `couponsTypes`.`maxCouponType`
FROM membres AS `M` 
INNER JOIN
(SELECT idMembre, MAX(coupons.`idType`) AS `maxCouponType`
   FROM coupons
   GROUP BY idmembre
) `couponsTypes`
ON couponstypes.idMembre = M.idMember
ORDER BY maxCouponType DESC

But, you don't need the membres table at all. Although referenced in the outer select, it is equivalent to the member id in the coupons type table. So, you can write your query as:

SELECT idMembre, MAX(coupons.`idType`) AS `maxCouponType`
FROM coupons
GROUP BY idmembre
ORDER BY 2 DESC

This is probably the simplest and most efficient way formulation.