List:General Discussion« Previous MessageNext Message »
From:Barry Date:April 10 2006 10:10am
Subject:Re: AS in a statement
View as plain text  
Peter Lauri wrote:
> Best group member,
> 
> I do this query 
> 
> SELECT tour_player_score.strokes AS score, tour_scorecard_hole.par AS par,
> score-par AS overpar
> FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 AND
> tour_scorecard_hole.id=tour_player_score.scorecard_hole_id
> 
> It gives error: [localhost] ERROR 1054: Unknown column 'score' in 'field
> list'
> 
> I know where the error comes from (the tables does not have score in them),
> but I want to simplify the query using the AS property, and continue in the
> query us it.
> 
> This works:
> 
> SELECT tour_player_score.strokes AS score, tour_scorecard_hole.par AS par,
> tour_player_score.strokes - tour_scorecard_hole.par AS overpar
> FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 AND
> tour_scorecard_hole.id=tour_player_score.scorecard_hole_id
> 
> But I do not want to use the long name... anyone with comments?
> 
> Best regards,
> Peter
> 
You could use the HAVING statement for this.

Not tested:
SELECT tour_player_score.strokes AS score, tour_scorecard_hole.par AS par
FROM `tour_player_score`, tour_scorecard_hole HAVING score-par AS 
overpar WHERE tour_player_id=175 AND 
tour_scorecard_hole.id=tour_player_score.scorecard_hole_id

Hmm was having before or after where (hmm)
test that also please:
SELECT tour_player_score.strokes AS score, tour_scorecard_hole.par AS par
FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 
AND tour_scorecard_hole.id=tour_player_score.scorecard_hole_id HAVING 
score-par AS overpar

Greets
	Barry
-- 
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
Thread
AS in a statementPeter Lauri10 Apr
  • Re: AS in a statementBarry10 Apr
Re: AS in a statementMartijn Tonies10 Apr