NYCPHP Meetup

[Stephen Musgrave]

The almighty list:

I am struggling with including a field returned from the subselect in  
the WHERE clause of the parent SELECT.  I keep on getting an error  
that the field is unknown:

"Unknown column 'pt_prgm_term_overall_end_date' in 'where clause'"

I have seen that this is a bug in previous versions of MySQL, but I  
am using 4.1.18 and it was to have been fixed by this version, 4.1.16  
I believe.

Here is the SQL below.  I have bolded the field in question [for  
those not using Pine :-)].  I have tried putting the restriction in  
the subselect's WHERE clause, but that simply returns all rows with  
the pt_prgm_term_overall_end_date blank if not matching '2007-04-30'

SELECT DISTINCT a.pt_prgm_id,
	a.user_id,
	b.pt_lname,
	b.pt_fname,
	(SELECT pt_prgm_term_start_date FROM carl_pt_prgm_term
		WHERE user_id = a.user_id AND pt_prgm_id = a.pt_prgm_id
		ORDER BY pt_prgm_term_start_date ASC LIMIT 1)
		AS pt_prgm_term_overall_start_date,			
	(SELECT pt_prgm_term_end_date FROM carl_pt_prgm_term
		WHERE user_id = a.user_id AND pt_prgm_id = a.pt_prgm_id
		ORDER BY pt_prgm_term_end_date DESC LIMIT 1)
		AS pt_prgm_term_overall_end_date						
FROM (carl_pt_prgm AS a, carl_pt AS b)
WHERE b.user_id = a.user_id
	AND a.prgm_id = 6
	AND a.pt_prgm_status_id = 'A'
	AND pt_prgm_term_overall_end_date = '2007-04-30'
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20060321/3bc761ed/attachment.html>