MySQL subquery problems

[Pig]

I am trying to sort by the result of a subquery, but I don't think I am referencing things correctly. I get the error: Unknown column 'qty' in 'where clause'. If I remove the WHERE and ORDER BY statements that use qty and fetch a row, I can get the qty value. Thanks.

SELECT 
	bat_team_id AS id, 
	bat_team_title AS title, 
	(
		SELECT 
			COUNT(ref_user_id)
		FROM 
			ref_user 
		WHERE 
			ref_user_team = bat_team_id
	) AS `qty`
FROM 
	bat_team
WHERE 
	bat_team_autoenroll = 1 AND 
	`qty` < 15
ORDER BY 
	`qty` 
LIMIT 
	0,1

[bpat1434]

Don't listen to me, go to the next post....

~Brett

[Lars_Berg]

You can't use correlation names in the where clause.

select id,title,qty from (
SELECT 
	bat_team_id AS id, 
	bat_team_title AS title, 
	(
		SELECT 
			COUNT(ref_user_id)
		FROM 
			ref_user 
		WHERE 
			ref_user_team = bat_team_id
	) AS qty
FROM 
	bat_team
WHERE 
	bat_team_autoenroll = 1) dt
where 
	qty < 15
ORDER BY 
	qty
LIMIT 
	0,1

or

select bat_team_id AS id, 
       bat_team_title AS title, 
       count(*) as qty
  from bat_team join ref_user on ref_user_team = bat_team_id
 group by bat_team_id,bat_team_title
  having count(*) < 15
  order by qty 
 limit 0,1

bpat1434:

In your example qty is the name of a derived table which can not be used in the way you have used it in the where clause. Also you can not refer to columns outside the scope of the derived table.