Victor Ribeiro da Silva Eloy Victor Ribeiro da Silva Eloy - 3 months ago 12
MySQL Question

how to fix correlated subquery on mysql

I'm trying to create a correlated subquery but I'm not able to figure out what i am doing wrong. Every time I try to run it I get Unknow colunm in 'on clause':

so given this schema

create table promotion(PK int, name varchar(255));
create table uiExperienceLevel(PK int, code varchar(255));
create table xp2promo(SourcePk int, TargetPk int);

insert into promotion values(1,'Desktop promotion');
insert into promotion values(2,'Ipad promotion');

insert into uiExperienceLevel values(1,'Desktop');
insert into uiExperienceLevel values(2,'Iphone');
insert into uiExperienceLevel values(3,'Ipad');

insert into xp2Promo values(1,1);
insert into xp2Promo values(2,2);


Why does this query not work?

SELECT promo.PK FROM promotion as promo
WHERE ('Desktop' in
(SELECT uiXpLevel.Code FROM xp2promo xp2promo
JOIN uiExperienceLevel uiXpLevel
ON uiXpLevel.PK = xp2promo.TargetPK
AND xp2promo.SourcePK = promo.PK )
)


every time I run this I get the following error.

Error Code: 1054. Unknown column 'promo.PK' in 'on clause'





PS: I know that I could do exactly the same thing using inner joins, what would not only work but have better performance as well

SELECT promo.Pk FROM Promotion as Promo
JOIN xp2promo xp2promo ON xp2promo.SourcePK = promo.PK
JOIN uiExperienceLevel uiXpLevel ON uiXpLevel.PK = xp2promo.TargetPK
WHERE uiXpLevel.Code ='Desktop'


however for academic purposes I would like to know how to fix the subquery




EDIT: ok I figured out why,

according to mysql official documentation https://dev.mysql.com/doc/refman/5.5/en/correlated-subqueries.html

enter image description here

so I just need to invert the logic to give the ids of promotions with desktop from the inner query and compare the pks outside, like this

SELECT promo.PK FROM promotion as promo
WHERE (promo.PK in
(SELECT xp2promo.SourcePk FROM xp2promo xp2promo
JOIN uiExperienceLevel uiXpLevel
ON uiXpLevel.PK = xp2promo.TargetPK
WHERE uiXpLevel.Code = 'Desktop' )
)


Anyway I'll keep the question as reference to anyone facing the same problem

Answer

You cannot access a table from sub query. Define instead the table once more in the subquery. Like this

SELECT  promo.PK  
FROM promotion as promo 
    WHERE ('Desktop' in 
            (SELECT  uiXpLevel.Code  FROM promotion as promoInner
                                     JOIN xp2promo xp2promo 
                                        ON xp2promo.SourcePK  =  promoInner.PK
                                     JOIN uiExperienceLevel uiXpLevel 
                                        ON  uiXpLevel.PK  =  xp2promo.TargetPK )
          )