Create View

[mtgriffiths]

Hi all,

 

When i am trying to add the below code to my database to create a view i am getting an error.

 

If possible could someone point out where i am going wrong please?

 

Thanks in advance

 

Matthew

 

CREATE VIEW Away_Points as select users.Team_Name AS Team_Name,sum(if((results.Goals2 > results.Goals1),3,if((results.Goals1 = results.Goals2),1,0))) ASAway_Points from (users join results on((users.user_id = results.Team2))) group by users.Team_Name order by sum(if((results.Goals2 > results.Goals1),3,if((results.Goals1 = results.Goals2),1,0))) ;

[fenway]

What's the error?

[mtgriffiths]

The error is:

 

#1064 - 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 'VIEW Away_Points as select users.Team_Name AS Team_Name,sum(if((results.Goals2 >' at line 1

 

 

[fenway]

You're missing "SELECT"!

[mtgriffiths]

CREATE VIEW Away_Points as select users.Team_Name

 

or do i have to have another select?

[fenway]

CREATE VIEW Away_Points as select users.Team_Name

 

or do i have to have another select?

Sorry, my eyes are tired today... what version of MySQL are you running?

[mtgriffiths]

Version is 5.0.45

 

I have been using phpmyadmin to create my tables tho

[mtgriffiths]

I have run the query on my localhost and it works fine.

 

When i run it on the internet i have the above error

[fenway]

I have run the query on my localhost and it works fine.

 

When i run it on the internet i have the above error

And you're sure that both are version 5?

[mtgriffiths]

Looking at it on the internet it says:

 

MySQL client version: 4.1.22

 

Im guessing that as its older it wont allow it?

 

Any idea how i can get around this as its very important to my assignment

 

Thanks again

[fenway]

That's right -- no views prior to v5+.

 

You could always make a temporary table....