My head is about to explode. I have been trying to figure this out now for a few days.
The query below works fine but I have another field I need to add.
I have another table called click_log which logs the companyID clicked, what I need to do is add the number of cliks for that company to the query.
I have tried COUNT(click_log.companyID) in alsorts of variations but I cannot get it right.
Can anybody help. I have laid the code out as clear as I can below. If any more info is needed just give us a shout.
SELECT bfpomerchants.companyID, bfpomerchants.company, bfpomerchants.cat1ID, bfpomerchants.cat2ID, bfpomerchants.cat3ID, DATE_FORMAT(bfpomerchants.date, '%%D %%b %%y') AS dateAdded, LEFT(bfpomerchants.`desc`, 100) AS short, bfpomerchants.URL, bfpomerchants.company, (SELECT bfpocats.cat FROM bfpocats WHERE bfpomerchants.cat1ID = bfpocats.catID ) AS cat1, (SELECT bfpocats.cat FROM bfpocats WHERE bfpomerchants.cat2ID = bfpocats.catID ) AS cat2, (SELECT bfpocats.cat FROM bfpocats WHERE bfpomerchants.cat3ID = bfpocats.catID ) AS cat3, (SELECT bfpocats.cat FROM bfpocats WHERE bfpomerchants.cat4ID = bfpocats.catID ) AS cat4 FROM bfpomerchants WHERE (bfpomerchants.cat1ID = '%s') OR (bfpomerchants.cat2ID = '%s') OR (bfpomerchants.cat3ID = '%s') OR (bfpomerchants.cat4ID = '%s')