I have the following mySQL query, and for some reason I can't get it working.

I have two tables:

exp_competition_purchase_upload_total contains a member id and total_uploads column. exp_channel_titles has lots of columns, but the two I need to check is author id and channel id.

My aim is to query the first table and locate any rows, find the member_id and take the upload_total associated with it. Then go to the second table, count the rows that have an author id that matches the member id from table one, and has a channel id of 1.

SELECT COUNT(*) AS uploads, c.upload_total 
FROM exp_competition_purchase_upload_total AS c
LEFT JOIN exp_channel_titles AS t ON c.member_id = t.author_id
WHERE c.member_id = '1'
AND t.channel_id = '1'

Currently the count is returning, but not the upload_total number. The following works:

SELECT upload_total 
FROM exp_competition_purchase_upload_total
WHERE member_id = '1'

If I'm not mistaking the problem is with your AND t.channel_id = '1' please try removing that and see the results.