How do you reference a table in a subquery?

[ballhogjoni]

Hey everyone,

 

I need to figure this out to get my query to work. I am writing a subquery to get specific data, but I need to get an id from a table outside of the subquery. Any ideas?

 

SELECT	
	c.id,
	c.code,
	c.name, 
	c.date_started, 
	c.num_employees, 
	c.city, 
	c.state, 
	all_sales.sales, 
	last_year_sales.sales AS last_year_number_of_sales, 
	this_year_sales.sales AS this_year_number_of_sales,
	count(clients.id) as num_clients
FROM travel_clients clients
LEFT JOIN travel_companies c ON c.id = clients.company_id
LEFT JOIN travel_company_lists cl ON c.id = cl.company_id 
LEFT JOIN (SELECT 
		cs.cid, 
		sum(cs.sale_count) - sum(cs.nosale_count) as sales 
			FROM travel_company_sales AS cs
				WHERE cs.cid = c.id) as all_sales ON all_sales.cid=c.id 
LEFT JOIN (SELECT 
		cs.cid, 
		sum(cs.sale_count) - sum(cs.nosale_count) as sales 
			FROM travel_company_sales AS cs 
				WHERE cs.year = 2007 AND cs.cid = c.id) as last_year_sales ON last_year_sales.cid=c.id 
LEFT JOIN (SELECT 
		cs.cid, 
		sum(cs.sale_count) - sum(cs.nosale_count) as sales 
			FROM travel_company_sales AS cs 
				WHERE cs.year = 2008 AND cs.cid = c.id) as this_year_sales ON this_year_sales.cid=c.id 	
WHERE approved = 1 AND deleted = 0 

GROUP BY c.id 

ORDER BY c.name ASC 

LIMIT 0,20;

 

As you can see in the subqueries WHERE clauses I say WHERE ... cs.cid = c.id. The c.id part is the id from the travel_companies that is outside the subquery. When I run this whole query I get the error: Unknown column 'c.id' in 'where clause'

[fenway]

That shouldn't be an issue... simply the query, make sure it works with a single one.