NYCPHP Meetup

[Jon Baer]

My guess ...

It looks like you are either not included the actual table it's being  
called from in your FROM clause, if I read it right, you have:

FROM (carl_pt_prgm AS a, carl_pt AS b)

Try adding FROM (carl_pt_prgm AS a, carl_pt AS b, carl_pt_prgm_term  
AS c)

And c.pt_prgm_term_overall_end_date

See if that works ... I think your overall statement is missing thus  
why the column can't be found.

- Jon

On Mar 21, 2006, at 11:28 AM, Stephen Musgrave wrote:

> 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'
> _______________________________________________
> New York PHP Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
> New York PHP Conference and Expo 2006
> http://www.nyphpcon.com
> Show Your Participation in New York PHP
> http://www.nyphp.org/show_participation.php