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.

You don't seem to use any join criteria, inner join ... ON ... — full.stack.ex, Dec 18 '12 at 21:31
I've also tried with the regular INNER JOIN format but I'm getting the same error for this column. `SELECT DISTINCT M.idMembre, couponsTypes.maxCouponType FROM membres AS M INNER JOIN (SELECT MAX(coupons.idType) AS maxCouponType FROM coupons) AS couponsTypes ON M.idMembre = coupons.idMembre ORDER BY maxCouponType DESC` — oliboon, Dec 18 '12 at 21:37
Using the alias for the coupons table gives me an unknown column issue too. — oliboon, Dec 18 '12 at 21:41

score 19 · Accepted Answer · edited Oct 27 '20 at 18:41

19

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.

edited Oct 27 '20 at 18:41

Mahmoud Abdelsattar

1,299
1
15
31

answered Dec 18 '12 at 21:36

Gordon Linoff

1,242,037
58
646
786

Is there a difference between the queries in the event that members are non-unique in the `membre` table? Can't quite get my head around that one, but I have only just got home from a rather boozy luncheon... – eggyal Dec 18 '12 at 21:40
@eggyal . . . In this case, there actually isn't a difference. The `distinct` removes duplicates. I wouldn't write the query that way, but there isn't a difference. – Gordon Linoff Dec 18 '12 at 21:42
I've tried the second format and it does work. However, I do need the membres tables because I use the query results to display the members on the page. I've tried with the group by but I still receive a unknown column for `M.idMembre`. I've edited the query in my question. – oliboon Dec 19 '12 at 14:10
@OlivierParenteau . . . That is because I mistakenly left the `where` clause in the subquery. I fixed it. – Gordon Linoff Dec 19 '12 at 14:15

score 2 · Answer 2 · answered Dec 18 '12 at 21:35

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

Unknown column in subquery where clause

2 Answers2

Linked