Unknown column in subquery where clause

Question

I'm having a problem in the where clause of my INNER JOIN subquery. I'm receiving a unknown column error for M.idMembre. I've tried using the table name instead of the alias but I get the same issue. I've also tried removing the WHERE clause from the subquery and adding this condition in the ON clause after the subquery. However, I'm having the same issue either way. I feel it's something obvious I'm missing here.

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

Let me know if you need more information.

Answer 1

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.

Answer 2

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