Complex Query problem

[grandadevans]

[b]FIXED![/b][size=1][color=#CC0000]
Hi,
I am in desperate need for help.
I am trying to create a page with a top 20 chart.
The problem I am having is that I can't get the COUNTS for last month and this months
ordered by the position this month descending.

The fields I am using are :

Table --> bfpomerchants
[blockquote]Appropriate fields --> companyID (INT) & company (VARCHAR)[/blockquote]
Table --> click_log
[blockquote]Appropriate fields --> clickID (INT), date (timestamp) & companyID (INT)[/blockquote]

I have tried all sorts of JOINS, COUNTS and every other method I know of.
I have figured out the
[code]WHERE MONTH(`date`) = MONTH(CURDATE())-1[/code]
for last month, and without the -1 for this month.
I can get the values for last month and this month from seperate statements,
but I need them in a seperate statement
This is what I'm using to get them seperately
[code]SELECT COUNT(c.clickID) AS lastMonth, b.company
FROM bfpomerchants b RIGHT JOIN click_log c USING (companyID)
WHERE MONTH(`date`) = MONTH(CURDATE())-1 AND b.company IS NOT NULL
GROUP BY b.company
ORDER BY lastMonth DESC;[/code]

I have tried doing it myself but ended up with a statement that didn't work and was taking up to
and just over 180 seconds.

Can anybody help me, if you need any info on the field structure then just ask.
Thanks
John

[grandadevans]

After all that it was just a simple query
[code]SELECT bfpomerchants.companyID, bfpomerchants.company,
(SELECT COUNT(*) FROM click_log
WHERE click_log.companyID = bfpomerchants.companyID AND
MONTH(`date`) = MONTH(CURDATE())-1) AS lastMonth,
(SELECT COUNT(*) FROM click_log
WHERE click_log.companyID = bfpomerchants.companyID AND
MONTH(`date`) = MONTH(CURDATE())) AS thisMonth
FROM bfpomerchants
ORDER BY thisMonth DESC[/code]

[fenway]

I was actually thinking that it was the fact that you couldn't use the index on `date` -- but if you got it working, then fine. Also, don't use RIGHT JOINs... ugh.