mysql - Multiple joins on same table SQL Server -

i have 1 table [item] supposed have thousands of records :

    id  itemno  departdate  country item    amount       1       1   2016-01-01  france  hotel   100      1       2   2016-01-01  france  hotel   150     1       3   2016-01-01  france  meals   150     2       1   2016-02-01  england hotel   150     2       1   2016-02-01  england meals   200     2       1   2016-02-02  england meals   200     2       1   2016-02-02  wales   meals   200 

i want result count on number of hotel, meal based on distinct departdate :

    country hotelcount  mealcount     france  1             1     england 1             2     wales   0             1        select i.country, count(distinct ihotel.departdate) hotelcount, count(distinct imeal.departdate) mealcount     item     left join item ihotel on i.id = v.id , i.country =ihotel.country      left join item imeal  on i.id = imeal .id , i.country =imeal .country      i.country not null     group i.country 

but not work thousands of records causes time out issue. kind ? lot.

in query, don't separate meal , hotel :

select i.country, count(distinct ihotel.departdate) hotelcount, count(distinct imeal.departdate) mealcount     item     left join item ihotel on i.id = v.id , i.country =ihotel.country      left join item imeal  on i.id = imeal .id , i.country =imeal .country      i.country not null     group i.country 

i not happy query , sql fiddle doesn't work again :

select i.country, max(coalesce(h.ct,0)) hotelcount  , max(coalesce(m.ct,0)) mealcount     item     left join     (select count(*) ct,country, item        item           item = 'hotel' group country, item) h       on h.country = i.country , i.item = h.item     left join     (select count(*) ct,country, item          item           item = 'meals' group country, item) m       on m.country = i.country , i.item = m.item     group i.country