CODEWITHSUNDEEP
mysqlsqldatabasesubquerymysql-workbench
Abdul Moiz
Abdul Moiz

Reputation: 1327

Unknown column issue

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, pap.allocation_id,pap.affiliate_id from paul.affiliates_price pap) pafp  
ON (dal.id=pafp.allocation_id and daf.id=pafp.affiliate_id) 
ORDER BY daf.id;

Issue: It say unknown column daf.id in on clause, although I have this column in the field list please help !!!.

Upvotes: 0

Views: 61

Answers (3)

T.Y. Kucuk
T.Y. Kucuk

Reputation: 477

You can use the affiliates_price tables' subquery as a table between FROM and WHERE clasue. It will work in this way.

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,
    (select pap.lead_price,pap.live, pap.allocation_id,pap.affiliate_id from paul.affiliates_price pap) pafp     
WHERE
    dal.id=pafp.allocation_id and daf.id=pafp.affiliate_id
ORDER BY daf.id;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270443

Try writing the FROM clause like this:

FROM paul.affiliates_price pafp JOIN
     degreeamerica.affiliates daf
     ON daf.id = pafp.affiliate_id JOIN
     degreeamerica.allocations dal 
     ON dal.id = pafp.allocation_id

The subquery is also unnecessary and it impedes the use of indexes (in MySQL).

Upvotes: 0

jarlh
jarlh

Reputation: 44786

JOIN chains are evaluated before comma separated tables. Switch to explicit JOINs all the way and it will work.

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 
     (select pap.lead_price,pap.live, pap.allocation_id,pap.affiliate_id
      from paul.affiliates_price pap) pafp  
JOIN
     degreeamerica.affiliates daf
     ON (daf.id = pafp.affiliate_id) 
JOIN
     degreeamerica.allocations dal ON 
     ON (dal.id = pafp.allocation_id) 
ORDER BY daf.id;

Upvotes: 1

Related Questions