subquery join on outerquery colum

[kevisazombie]

Hi all, I am getting this error "Unknown column 'distros.id' in 'where clause'" From the following query. I know the error is coming from the second line where I am trying to do a count on the  distinct_downloads subquery. I need the sub query's distros.id to match up with the outer query's corresponding row distro.id. I'm not that sharp on mysql, this query is probably poorly written. Any other tips would be greatly appreciated

 

SELECT DISTINCT downloads . * , products.title, products.company, categories.category_name, compatibilities.name AS compatibility, distros.distro_type, distros.filename, distros.url,
					(SELECT COUNT( * )FROM (SELECT * FROM downloads WHERE downloads.distro_id = distros.id AND downloads.ip_address != '127.0.0.1' AND DAY(date) = DAY(CURDATE())   GROUP BY distro_id, ip_address) AS distinct_downloads) AS downloads,
					FROM `downloads`
					INNER JOIN products ON downloads.product_id = products.product_id
					INNER JOIN distros ON downloads.distro_id = distros.id
					INNER JOIN categories ON products.category_id = categories.category_id
					INNER JOIN compatibilities ON compatibilities.compatibility_id = distros.compatibility_id
					WHERE ip_address != '127.0.0.1'
					AND DAY(date) = DAY(CURDATE())  
					GROUP BY distro_id, product_id
					ORDER BY DAY(downloads.date), downloads DESC
					LIMIT 0 , 15 

[Ken2k7]

Well, you only selected from downloads table and not distros. And I don't think the GROUP BY makes sense because it'll most likely return more than one row. Also,  Maybe you meant -

SELECT COUNT(*) AS downloads
FROM downloads do
INNER JOIN distros di
     ON do.distro_id = di.id
WHERE do.ip_address != '127.0.0.1'
     AND DAY(do.date) = DAY(CURDATE())