473,323 Members | 1,547 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

How Can I Correct this Select AS Syntax?

7
I'm seeking to use Select AS. Here's my query:

Expand|Select|Wrap|Line Numbers
  1. select  *  from new_alloys_added_by_users  left join ratings_for_fz_scores on new_alloys_added_by_users.Alloy_ID_No = ratings_for_fz_scores.Alloy_ID_No AS NewAlloysAndRatings
  2.  
I get this error message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS NewAlloysAndRatings' at line 1
How can I correct this?

Thanks in advance to all for any info.
Jan 1 '09 #1
3 3265
Atli
5,058 Expert 4TB
Hi.

In that context, it makes no sense to add an AS clause.
The table name is not returned by the query, so changing it makes no difference, which is why the syntax for it is not recognized by MySQL.

You can only use the AS clause on tables where they can actually be used by another part of the query, like in JOINS or sub-selects.

For example:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   `subTable`.`field1` AS 'First',
  3.   `subTable`.`field2` AS 'Second',
  4.   `joinTable`.`field3` AS 'Third'
  5. FROM (
  6.   SELECT * FROM someTable
  7.   ORDER BY `someField`
  8. ) AS `subTable`
  9. LEFT JOIN `someOtherTable` AS `joinTable`
  10.   ON `subTable`.`idField` = `joinTable`.`FKfield`;
  11.  
There you see two legit uses of the AS clause to create table name aliases, as well as one way to create a field name alias.
Jan 1 '09 #2
VikR
7
Thanks very much for this info, Atli. I have two queries that are working correctly. I need to join the results. Since SELECT AS is not the correct approach, what approach may I use for this purpose? Here are the two queries.

Expand|Select|Wrap|Line Numbers
  1. SELECT  *  from new_alloys_added_by_users  left join ratings_for_fz_scores on new_alloys_added_by_users.Alloy_ID_No = ratings_for_fz_scores.Alloy_ID_No
  2.  
  3. SELECT * 
  4.  FROM user_prefs_for_alloys
  5.  JOIN new_alloys_added_by_users ON user_prefs_for_alloys.Alloy_ID_No = new_alloys_added_by_users.Alloy_ID_No
  6.  WHERE (user_prefs_for_alloys.id = 1)
  7.  AND (user_prefs_for_alloys.Alloy_ID_No >0)
Both queries are working correctly. What is the correct way to join the results? Thanks very much in advance for any info.
Expand|Select|Wrap|Line Numbers
  1. +----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  2. | Table                | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
  3. +----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  4. | user_prefs_for_alloys | CREATE TABLE `user_prefs_for_alloys` (
  5.   `id` int(11) NOT NULL,
  6.   `NDB_No` int(11) NOT NULL,
  7.   `Alloy_ID_No` int(11) NOT NULL,
  8.   `min` float NOT NULL,
  9.   `max` float NOT NULL,
  10.   `final_amount` float NOT NULL default '0',
  11.   `fz_units_id` varchar(15) NOT NULL,
  12.   `used_in_last_alloy_plan` tinyint(1) NOT NULL,
  13.   KEY `id` (`id`),
  14.   KEY `id_2` (`id`,`NDB_No`),
  15.   KEY `id_3` (`id`,`Alloy_ID_No`)
  16. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
  17. +----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  18. 1 rows in set (0.04 sec)
  19.  
Expand|Select|Wrap|Line Numbers
  1. mysql> SHOW CREATE TABLE new_alloys_added_by_users;
  2. +--------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | Table                    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
  4. +--------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | new_alloys_added_by_users | CREATE TABLE `new_alloys_added_by_users` (
  6.   `Alloy_ID_No` int(11) NOT NULL auto_increment,
  7.   `Long_Desc` varchar(200) NOT NULL,
  8.   `brand_name` varchar(75) NOT NULL,
  9.   `product_name` varchar(75) NOT NULL,
  10.   `description` varchar(35) NOT NULL,
  11.   `id_of_user_who_added_this` int(11) NOT NULL COMMENT 'The ID of the User who Added It',
  12.   `fz_score` int(11) NOT NULL,
  13.   `date_added` varchar(12) NOT NULL default '',
  14.   `let_other_people_use_this_info` tinyint(1) NOT NULL,
  15.   PRIMARY KEY  (`Alloy_ID_No`),
  16.   KEY `Alloy_ID_No` (`Alloy_ID_No`),
  17.   KEY `Long_Desc` (`Long_Desc`,`id_of_user_who_added_this`),
  18.   KEY `Long_Desc_2` (`Long_Desc`,`let_other_people_use_this_info`)
  19. ) ENGINE=MyISAM AUTO_INCREMENT=125 DEFAULT CHARSET=latin1 |
  20. +--------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  21. 1 rows in set (0.05 sec)
  22.  
Expand|Select|Wrap|Line Numbers
  1. mysql> SHOW CREATE TABLE ratings_for_fz_scores
  2.     -> ;
  3. +-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  4. | Table                 | Create Table                                                                                                                                                                                                                                                                                                                 |
  5. +-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  6. | ratings_for_fz_scores | CREATE TABLE `ratings_for_fz_scores` (
  7.   `Alloy_ID_No` int(11) NOT NULL,
  8.   `id_of_user_who_provided_this_rating` int(11) NOT NULL,
  9.   `rating_was_a_plus` tinyint(1) NOT NULL,
  10.   KEY `Alloy_ID_No` (`Alloy_ID_No`,`id_of_user_who_provided_this_rating`)
  11. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
  12. +-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  13. 1 rows in set (0.04 sec)
  14.  
Jan 1 '09 #3
VikR
7
I think I just got it working using Views.
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE VIEW NewAlloysPlusRatings AS 
  2. SELECT  brand_name, date_added, description, new_alloys_added_by_users.Alloy_ID_No, fz_score, id_of_user_who_added_this, id_of_user_who_provided_this_rating, let_other_people_use_this_info, Long_Desc, product_name, rating_was_a_plus
  3. from new_alloys_added_by_users left join ratings_for_fz_scores on new_alloys_added_by_users.Alloy_ID_No = ratings_for_fz_scores.Alloy_ID_No ;
  4.  
  5. CREATE OR REPLACE VIEW UserPrefsPlusNewAlloys AS 
  6. SELECT id, NDB_No, min, max, final_amount, user_prefs_for_alloys.Alloy_ID_No
  7.  FROM user_prefs_for_alloys
  8.  JOIN new_alloys_added_by_users ON user_prefs_for_alloys.Alloy_ID_No = new_alloys_added_by_users.Alloy_ID_No
  9.  WHERE (user_prefs_for_alloys.id = 1)
  10.  AND (user_prefs_for_alloys.Alloy_ID_No >0);
  11.  
  12. SELECT *
  13.  from NewAlloysPlusRatings JOIN UserPrefsPlusNewAlloys on NewAlloysPlusRatings.Alloy_ID_No = UserPrefsPlusNewAlloys.Alloy_ID_No;
Jan 2 '09 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: j erickson | last post by:
with the following xsl and xml file, the display of the gif file with the <image/url> tag works. However, the gif file in the <description> tag using the name attribute "src" won't make the correct...
1
by: John Hall | last post by:
We need to read a SQL database containing a mix of English words and Chinese Characters. We think we need to use the N'xxxx' to read the Unicode. We have one place where the SELECT statement...
5
by: Adfra | last post by:
Hi, Situation is: Used an embedded query which was "optimized" be Access with Brackets ("()" -> "."). Now problem is: I get the error "Syntaxerror in From part" in normal view as well as in...
7
by: kosta | last post by:
hello! one of my forms communicates with a database, and is supposed to add a row to a table using an Insert statement... however, I get a 'oledb - syntax error' exception... I have double...
0
by: butterflyTee | last post by:
USING:ORACLE 9i For each of the following tasks, determine (a) the SQL statement needed to perform the stated task using the traditional approach and (b) the SQL statement needed to perform the...
1
by: terryspanky | last post by:
----------------------Below are all the codes don't have errors---- The only problem I have is when I Delete, I'ts not deleting the subject that I click. I want to use the above codes to modify the...
5
by: sam | last post by:
Why the folowing code generates ans error in the select expression ?: DataRow drRows = dsRevues.Tables.Select("year >= '2000' && year <= '2007'");// What is the correct syntax ? Sam
6
Soniad
by: Soniad | last post by:
Hello, I am excecuting a stored procedure in my ASP page , it has one out parameter (@confirm) . after executing the procedure i want to retreive this out parameter and assign it to variable...
0
by: Zettai | last post by:
Hi All, I am trying to query a mysql database using the 'LIKE' keyword within my query based on what the user has input. It works if I pass the string, however I am having trouble getting it to...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.