CREATE VIEW fails, but works as just a SELECT
I'm stuck at a really frustrating problem. I'm fairly new to MySQL, but have a reasonable knowledge of other databases(primarily Access and dBase).
If I run the select statement, it runs fine. If I add on 'CREATE VIEW xxx AS ' in front of it, I keep getting error 1060, Duplicate Column Name.
What I have is 6 tables that together make up all my world info data. The foreign key column has the same field name as the column in the table linked to it. It seems that the duplicate columns are removed during the join in the select statement, but when trying to turn that into a view, it's no longer removing the duplicate columns.
Tables are all InnoDB. I can post their structure but I dont think it's relevant? I changed the database name to 'db' in this example but that's not what it is just in case someone is going to say 'db' is a reserved word and could be the problem.
Here's what i'm trying:
CREATE VIEW db.GeoData AS
SELECT *
FROM db.tblgeoFeature F
JOIN db.tblgeoFeatureType FT ON (FT.FeatureType_str_code = F.FeatureType_str_code)
JOIN db.tblgeoAdmin1 A1 ON (A1.Admin1_str_code = F.Admin1_str_code)
JOIN db.tblgeoCountry C ON (C.Country_str_code = F.Country_str_code)
JOIN db.tblgeoRegion R ON (C.Region_str_code = R.Region_str_code)
ORDER BY FT.FeatureType_str_type, C.Country_str_name, A1.Admin1_str_name, F.Feature_str_name;
ERROR 1060 (42S21):Duplicate column name 'FeatureType_str_code'
The bit that has me confused, is if I do the exact same thing without the top CREATE VIEW line, it works perfectly. I realise I could type out all the fields instead of a *, but there are probably 50-60 fields, and more importantly I want to know why this happens and how to get around it so I know a proper solution not a workaround. The CREATE VIEW doesn't work if I leave out the ORDER BY segment either if that matters.
Using MySQL Community Edition 5.0.27, but it didn't work in 5.0.24 either.