I have 2 tables called NO2_avg and temperature_avg, I'm trying to join this tables on common column DATE(Date Time), but I'm getting following error.

ERROR 1064 (42000): 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 '"DATE(`Date Time`)" = a2."DATE(`Date Time`)"' at line 4

Tables info:

NO2_avg

 ---------------------- ------------------- ---------- -------------------- 
| Location             | DATE(`Date Time`) | COUNT(*) | avg(NO2)           |
 ---------------------- ------------------- ---------- -------------------- 
| Temple Way           | 2017-09-07        |       24 | 27.305995000000006 |
| Parson Street School | 2017-09-07        |       24 | 32.020833333333336 |
| Fishponds Road       | 2017-09-07        |       24 |             39.125 |
| Wells Road           | 2017-09-07        |       24 | 41.354166666666664 |
| Brislington Depot    | 2017-09-07        |       24 |           23.09375 |
 ---------------------- ------------------- ---------- -------------------- 

temperature_avg

 ------------------- ---------- ------------------- 
| DATE(`Date Time`) | COUNT(*) | avg(Temperature)  |
 ------------------- ---------- ------------------- 
| 2017-09-07        |       48 | 4.791666666666667 |
| 2022-03-04        |       48 | 5.333333333333333 |
| 2022-03-03        |       48 |            7.5625 |
| 2022-03-02        |       44 | 7.090909090909091 |
| 2022-03-01        |       48 | 5.791666666666667 |
 ------------------- ---------- ------------------- 

Query that I used :

SELECT `avg(NO2)`
FROM
     NO2_avg a1
INNER JOIN Temperature_avg a2 ON a1."DATE(`Date Time`)" = a2."DATE(`Date Time`)";

Please help me ...

CodePudding user response：

Sounds like the table was created some time in the past from a SELECT query that didn't define column aliases.

First I would urge you to change the column names to something easier to use. You can use columns whose names contain special characters but it's unnecessarily difficult.

MySQL 8.0 supports a RENAME COLUMN option:

mysql> alter table NO2_avg 
 rename column `DATE(``Date Time``)` TO date, 
 rename column `COUNT(*)` to count, 
 rename column `avg(NO2)` to avg;

Note that you need to delimit the column names, and those that contain literal back-tick characters need to treat those characters as special, either double the back-tick, or use a backslash to escape it.

If you can't rename the columns, then you must delimit the column names with back-ticks and treat literal back-ticks as special as noted above.

Standard SQL supports double-quotes (") as identifier delimiters, but in MySQL, double-quotes are string delimiters unless you set the SQL mode to include ANSI_QUOTES or ANSI.

CodePudding user response：

You just need to use double back tick (``) instead of single back tick (`).

Schema and insert statements:

 create table NO2_avg ( Location VARCHAR(50),`DATE(``Date Time``)` DATE  , `COUNT(*)` int, `avg(NO2)`  float);

 insert into NO2_avg values('Temple Way'           , '2017-09-07'        ,       24 , 27.305995000000006 );
 insert into NO2_avg values('Parson Street School' , '2017-09-07'        ,       24 , 32.020833333333336 );
 insert into NO2_avg values('Fishponds Road'       , '2017-09-07'        ,       24 ,             39.125 );
 insert into NO2_avg values('Wells Road'           , '2017-09-07'        ,       24 , 41.354166666666664 );
 insert into NO2_avg values('Brislington Depot'    , '2017-09-07'        ,       24 ,           23.09375 );

 create table temperature_avg (`DATE(``Date Time``)` date, `COUNT(*)` int, `avg(Temperature)` float  );

 insert into temperature_avg values('2017-09-07'        ,       48 , 4.791666666666667 );
 insert into temperature_avg values('2022-03-04'        ,       48 , 5.333333333333333 );
 insert into temperature_avg values('2022-03-03'        ,       48 ,            7.5625 );
 insert into temperature_avg values('2022-03-02'        ,       44 , 7.090909090909091 );
 insert into temperature_avg values('2022-03-01'        ,       48 , 5.791666666666667 );

Query:

 SELECT `avg(NO2)`
 FROM
      NO2_avg a1
 INNER JOIN temperature_avg a2 ON a1.`DATE(``Date Time``)` = a2.`DATE(``Date Time``)`;

Output:

db<>fiddle here