mysql - 简单的连接查询在本地主机中工作但在服务器中不起作用？

Question

以下查询在本地主机上运行良好并返回行，但是当在服务器中执行时，它返回错误...

显示的错误是

#1054 - Unknown column 'hc.id' in 'on clause'

什么问题？

select 
hd.holiday_id,h.same_date, h.holiday, hd.date 
from holiday_dates as hd 
join holidays as h on hd.holiday_id=hc.id 
join holiday_countries as hc on hc.holiday_id=h.id and hc.country_id=c.id 
join countries as c 
where 
c.name='india' and hd.year='2010'

我的表结构是国家

'id', 'int(11)', '', 'PRI', '', 'auto_increment'
'name', 'varchar(80)', 'YES', '', '', ''

假期

'id', 'int(11)', '', 'PRI', '', 'auto_increment'
'holiday', 'varchar(90)', 'YES', '', '', ''
'same_date', 'tinyint(1)', 'YES', '', '', ''
'religions', 'varchar(50)', '', '', '', ''
'season', 'enum('Winter','Spring','Summer','Autumn')', '', '', 'Winter', ''
'rate', 'int(2)', '', '', '0', ''

holiday_countries

'id', 'int(11)', '', 'PRI', '', 'auto_increment'
'holiday_id', 'int(11)', '', '', '0', ''
'country_id', 'int(11)', '', '', '0', ''
'link', 'varchar(40)', '', '', '', ''

假期日期

'holiday_id', 'int(11)', 'YES', 'MUL', '', '' //  this refers to the holiday_id from holiday_countries table
'year', 'varchar(4)', 'YES', '', '', ''
'date', 'date', '', '', '0000-00-00', ''

Answer 1

你的连接顺序被打乱了，看起来像是我第六行末尾的拼写错误

    select hd.holiday_id
         , h.same_date
         , h.holiday
         , hd.date 
      from holiday_dates as hd 
      join holidays as h on hd.holiday_id = h.id 
      join holiday_countries as hc on hc.holiday_id = h.id 
      join countries as c on hc.country_id = c.id 
     where c.name='india'
       and hd.year='2010'

已修复，错过了第 7 行末尾的 和 c.id

为您提供更多信息:抛出这些错误是因为您引用表中尚未连接且尚未创建别名的字段。

回到你原来的查询:

    select hd.holiday_id
         , h.same_date
         , h.holiday
         , hd.date 
      from holiday_dates as hd
      join holidays as h on hd.holiday_id=hc.id
      join holiday_countries as hc on hc.holiday_id=h.id and hc.country_id=c.id 
      join countries as c 
     where c.name='india'
       and hd.year='2010'

您最初的错误是因为您在第 6 行引用了表 hc，但在第 7 行加入了它并创建了别名。第二个错误是因为您在第 7 行引用了表 c，但在第 7 行加入了表并创建了别名8.

编辑:

如果没有表结构，这对我来说没有多大逻辑意义，但试试这个:

   select hd.holiday_id
         , h.same_date
         , h.holiday
         , hd.date 
      from holidays as h
      join holiday_countries as hc on hc.holiday_id = h.id 
      join holiday_dates as hd on hd.holiday_id = hc.id 
      join countries as c on hc.country_id = c.id 
     where c.name='india'
       and hd.year='2010'

享受，