Need to combine two quries into one

[n1concepts]

Hi,

 

I have two MySQL queries - posted below that pulls the select info I need and without delay.

However, i need to - somehow - combine those two queries because I need the total count for (l.reqid) from leads table added to the results from the clicks table.

 

I've tried all possible joing adding COUNT(l.reqid) from leads to the other sql query, however, it hogs the CPU resources and takes forever so i know I'm preparing the statement wrong.

Would appreciate some insight.

 

BTW: not every 'reqid' found in the 'clicks' table logged in the 'leads' table so that's definitely "LEFT JOIN so non-matches in leads is NULL.

 

Ok, here are the two queries - if anyone can suggest how best to combine the two, much appreciated (duplicate columns can be eliminated as they the same in each query).

 

# THIS QUERY PULLS THE TOTAL LEAD COUNT VIA 'l.reqid' - that's the only required column. The rest duplicates to 2nd query

select count(l.reqid) as lead_totals, a.aname, c.camp_name,c.campid,c.payout from leads as l 
JOIN affiliates as a ON l.affid = a.affid 
JOIN campaign as c ON l.campid = c.campid 
group by a.aname,c.camp_name;
---------------------------------------


# THIS QUERY PULLS TOTAL CLICK COUNT - MATCHING TO SPECIFIED AFFILIATES - WORKS JUST LIKE I NEED (I just need to combine the two so both TOTAL LEADS and TOTAL CLICKS IN SET RESULTS

select count(cl.reqid), a.aname,c.camp_name from clicks as cl 
JOIN affiliates as a ON cl.affid_sid = a.affid
JOIN campaign as c ON cl.camp_sid = c.campid 
group by a.aname;

Again, I need to, somehow, combine those two queries where not all row match from 'leads' table, but every row from 'clicks' table counted - thus, NULL will result in 'leads' table if LEFT JOINED to 'clicks'.

 

[n1concepts]

I found a solution - just created two separate tables to house the tota counts (results) then queries those in the main table which combined all the columns i needed to complete the desired results.

Note the best method but, 'hey?' - it works and not taxing the CPU as before....

 

However, if anyone have better suggestion on combining the two queries, i would love to hear it....

[n1concepts]

I figured it out combining the query after going back to the text books...

 

select count(cl.reqid),
(select count(l.reqid) as total_leads from leads as l  
where (a.affid = l.affid)  
AND (c.campid = l.campid)),
a.affid,a.aname,c.campid,c.payout,c.camp_name from clicks as cl 
JOIN affiliates as a ON cl.affid_sid = a.affid
JOIN campaign as c ON cl.camp_sid = c.campid 
group by a.aname,c.camp_name;