Re: AS in a statement
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) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AS in a statement
> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AS in a statement
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
