0

I have 3 columns in Database tables. Id Operator , o.Id Country

select [o.Id Operator] as OperatorID,[o.Id Country] as countryid
from 
customersubscription cs inner join club cl on (cs.idclub = cl.idclub) 
inner join customer cu on (cs.idcustomer = cu.idcustomer)
inner join operator o on (cu.idoperator = o.idoperator) 
left outer join promoter p on (cs.idpromoter = p.id )
where 
cl.idproject in (3,19,23,24,27)

The two columns in the select sections have spaces in them. I used [ ] and tried as mentioned in the question here but didnt work.

Community
  • 1
  • 1
Ahmed
  • 3
  • 3

2 Answers2

1

In MySQL any column name that isn't simple (composed of letters, numbers, and/or underscores) must be escaped:

SELECT o.`Id Operator` as OperatorID,
  o.`Id Country` as countryid

If you're calling the one OperatorID, the next should be CountryID for consistency's sake.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.31-log]Unknown column 'o.Id Operator' in 'field list' – Ahmed Apr 09 '17 at 06:52
  • Are you sure you put the backticks in the right place? Are you sure that's the right column name? You can check with `DESCRIBE operator`. – tadman Apr 09 '17 at 06:55
  • How do I check with the describe operator please? – Ahmed Apr 09 '17 at 06:59
  • Based on your question `operator` is the table in question. If you want a visual tool to help with this, [MySQL Workbench](https://www.mysql.com/products/workbench/) is pretty good and free. – tadman Apr 09 '17 at 07:02
  • Well, the name is okay, ` `s are placed on the right place as well. What can be the problem – Ahmed Apr 09 '17 at 07:03
  • 1
    This is how you escape things. As to what's wrong, I don't know. Maybe that column's not right. Maybe it has *two* spaces. – tadman Apr 09 '17 at 07:05
  • @Ahmed, the error message in the earlier comment, `Unknown column 'o.Id Operator' ` *strongly* suggests incorrect backtick placement of `\`o.Id Operator\`` instead of correct placement, which would be `o.\`Id Operator\``. The error shown can really have no other explanation. – Michael - sqlbot Apr 09 '17 at 07:26
  • Something strange. I type o.``Id Operator`` but in the error it shows ``o.Id Operator`` – Ahmed Apr 09 '17 at 08:01
0

You can quote column names with [/] in SQLite, but then anythying between the brackets will be part of the column name.

So the column name you a trying to use is o.Id Operator.

The period that separates the table name and the column name must not be quoted. And in MySQL, you must use backticks instead:

SELECT o.`Id Operator` ...
CL.
  • 173,858
  • 17
  • 217
  • 259
  • [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.31-log]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 '[Id Operator] as OperatorID,o.[Id Country] as countryid, cl.name as project, cl.' at line 3 – Ahmed Apr 09 '17 at 06:46
  • was lazy to type – Ahmed Apr 09 '17 at 06:46