php - 如何使用join和多个where条件？

Question

我有一个具有以下结构的数据库

我的表结构是国家/地区

'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', ''

我使用以下查询来获取特定国家和特定年份的假期

 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'

示例输出将是这样的，

975, 1, 'Republic Day', '2010-01-26'
976, , 'India Independence Day', '2010-08-15'
977, 1, 'Gandhi Jayanti (Mahatma Gandhi birthday)', '2010-10-02'

但我需要再列出两年的日期，所需的输出将是这样的，

id  same_date      holiday                    2010           2011        2012
975    1        'Republic Day'            '2010-01-26'  '2011-02-29' '2012-03-26'
976    0        'India Independence Day'  '2010-08-15'  '2011-08-15' '2012-08-15'
977    1        'Gandhi Jayanti'          '2010-10-02'  '2011-10-02' '2012-10-02'

如何查询？

Answer 1

SELECT  h.same_date,
        h.holiday,
        hd2010.date,
        hd2011.date,
        hd2012.date
FROM    countries c
JOIN    holiday_countries hc
ON      hc.country_id = c.id
JOIN    holidays h
ON      h.id = hc.holiday_id
LEFT JOIN
        holiday_dates hd2010
ON      hd2010.holiday_id = hc.holiday_id
        AND hd2010.year = '2010'
LEFT JOIN
        holiday_dates hd2011
ON      hd2011.holiday_id = hc.holiday_id
        AND hd2011.year = '2011'
LEFT JOIN
        holiday_dates hd2012
ON      hd2012.holiday_id = hc.holiday_id
        AND hd2012.year = '2012'
WHERE   c.name = 'india'