CODEWITHSUNDEEP
mysqlsqldatabasesubquerymysql-workbench
Abdul Moiz
Abdul Moiz

Reputation: 1327

Optimize query with multiple column sub query

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,

   (select lead_price from paul.affiliates_price where affiliate_id = daf.id and allocation_id = dal.id) as lead_price,

   (select live from paul.affiliates_price where affiliate_id = daf.id and allocation_id = dal.id) as live,

   (select allocation from paul.affiliates_price where affiliate_id = daf.id and allocation_id = dal.id) as allocation

FROM 

     degreeamerica.affiliates daf, degreeamerica.allocations dal;

ORDER BY daf.id;

Can any one please help in optimizing this query using multiple column sub query? Please help!!!

I have already tried this:

 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;

Its causing error. Unknown daf.id I am using Cartesian product with multiple column sub query. I have already spend 8 to 9 hours on this. Please help. Tell me the mistake in my query the second one or optimize the first one for me. Please help.

Upvotes: 0

Views: 43

Answers (2)

JamieD77
JamieD77

Reputation: 13949

you can try this. not sure if it's really optimizing anything.

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,
   p.lead_price,
   p.live,
   p.allocation
FROM 
     degreeamerica.affiliates daf, degreeamerica.allocations dal, paul.affiliates_price p
WHERE 
    p.affiliate_id = daf.id and p.allocation_id = dal.id
ORDER BY 
    daf.id

You can also CROSS JOIN affiliates and allocations then LEFT JOIN affiliates_price

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,
    p.lead_price,
    p.live,
    p.allocation
FROM 
    degreeamerica.affiliates daf
    CROSS JOIN degreeamerica.allocations dal 
    LEFT JOIN paul.affiliates_price p ON p.affiliate_id = daf.id and p.allocation_id = dal.id
ORDER BY 
    daf.id

Upvotes: 0

Holmes IV
Holmes IV

Reputation: 1749

I think this would give you what you want 

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
   ,ap.lead_Price
   ,ap.live 
   ,ap.allocation 
FROM 

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

ORDER BY daf.id;

Upvotes: 1

Related Questions