mysql - 多列子查询不适用于笛卡尔积

Question

SELECT 
   daf.id as affiliate_id,
   daf.name as affiliate_name,
   dal.name as allocation_name,
   dal.id as allocation_id,
   dal.allocation,dal.price
FROM 
     degreeamerica.affiliates daf,degreeamerica.allocations dal
JOIN 
     (select pap.lead_price,pap.live from paul.affiliates_price pap) pafp  
ON (dal.id=pafp.allocation_id and daf.id=pafp.affiliate_id) 
ORDER BY daf.id;

未知列 pafp.allocation_id。我有这个专栏。但它不起作用。请帮忙。

Answer 1

您的子查询中似乎未选择“allocation_id”列。在子查询选定列中添加此列并检查。

SELECT 
   daf.id as affiliate_id,
   daf.name as affiliate_name,
   dal.name as allocation_name,
   dal.id as allocation_id,
   dal.allocation,dal.price
FROM 
     degreeamerica.affiliates daf,degreeamerica.allocations dal
JOIN 
     (select pap.lead_price,pap.live, allocation_id from paul.affiliates_price pap) pafp  
ON (dal.id=pafp.allocation_id and daf.id=pafp.affiliate_id) 
ORDER BY daf.id;