SELECT ID, Title, Name, Currency, StartCity, Duration, Price, OldPrice, Image
FROM
(SELECT ID, Title, Name, Currency, StartCity, Duration, Image, WebOrder,
CASE
WHEN PromoPrice <> 0 THEN PromoPrice
WHEN SpecialPrice <> 0 THEN SpecialPrice
ELSE NormalPrice
END as Price,
CASE
WHEN SpecialPrice <> 0 THEN SpecialPrice
ELSE NormalPrice
END as OldPrice
FROM
(SELECT ID, Title, Name, Currency, StartCity, Duration, Image, WebOrder,
IFNULL((SELECT CASE
WHEN defaultCategory = 5 THEN MIN(catPROM)
WHEN defaultCategory = 6 THEN MIN(catT)
WHEN defaultCategory = 1 THEN MIN(catTS)
WHEN defaultCategory = 2 THEN MIN(catP)
WHEN defaultCategory = 3 THEN MIN(catPS)
WHEN defaultCategory = 4 THEN MIN(catL)
END
FROM precios_by_day
WHERE precios_by_day.post_id = tours.ID
AND day > NOW()
AND roomType='DBL'
),0) as NormalPrice,
IFNULL((SELECT CASE
WHEN defaultCategory = 5 THEN MIN(catPROM)
WHEN defaultCategory = 6 THEN MIN(catT)
WHEN defaultCategory = 1 THEN MIN(catTS)
WHEN defaultCategory = 2 THEN MIN(catP)
WHEN defaultCategory = 3 THEN MIN(catPS)
WHEN defaultCategory = 4 THEN MIN(catL)
END
FROM precios_by_day_special
WHERE precios_by_day_special.post_id = tours.ID
AND day > NOW()
AND roomType='DBL'
AND agencyId = 0
),0) as SpecialPrice,
IFNULL((SELECT CASE
WHEN defaultCategory = 5 THEN MIN(catPROM)
WHEN defaultCategory = 6 THEN MIN(catT)
WHEN defaultCategory = 1 THEN MIN(catTS)
WHEN defaultCategory = 2 THEN MIN(catP)
WHEN defaultCategory = 3 THEN MIN(catPS)
WHEN defaultCategory = 4 THEN MIN(catL)
END
FROM promos_by_day
WHERE promos_by_day.post_id = tours.ID
AND day > NOW()
AND roomType='DBL'
AND FreeQuota > 0
),0) as PromoPrice
FROM tours
WHERE Status = 'publish') as derivedTable
) as derivedTable2
WHERE
CASE
WHEN EXISTS (SELECT NewMasterTourId FROM flightToursCreated WHERE NewMasterTourId = (SELECT MasterTourId FROM tourRefs WHERE PostId = derivedTable2.ID LIMIT 1))
THEN EXISTS (SELECT id from precios_by_day WHERE Post_Id = derivedTable2.Id)
ELSE 1=1
END
AND Price <> 0
ORDER BY price desc
LIMIT 0,21;
