List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:April 10 2006 10:06am
Subject:Re: AS in a statement
View as plain text  

> 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?

First things first... IMO, you should ALWAYS use full tables names or
table aliasses before columns as soon as you're using more than 1 table
in your query.

eg:
select tps.strokes, tsh.par
from tour_player_score tps join tour_scorecard_hole tsh
on (tsh.id = tps.scorecard_hole_id)
where tps.tour_player_id = 175

As you can see, you can alias your table names and things should
be easier for you.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Thread
AS in a statementPeter Lauri10 Apr
  • Re: AS in a statementBarry10 Apr
Re: AS in a statementMartijn Tonies10 Apr