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'