been away for a while and am obviously rusty.
Please assume 'today' is Saturday.
I am trying to output the days on which a product is available either side of today. So it could be Thursday and Tuesday. I have tried using the following query to return the max(day_of_week) before today - which is in fact Friday but, it returns 'Monday'.
here's the table
CREATE TABLE `product_times` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`product_on_sale_id` bigint(20) NOT NULL,
`day_of_week` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
`time_of_day` time DEFAULT NULL,
`max_spaces` varchar(6) COLLATE utf8_unicode_ci DEFAULT NULL,
`spaces_sold` int(11) DEFAULT NULL,
`remaining_spaces` varchar(6) COLLATE utf8_unicode_ci DEFAULT NULL,
`on_off` char(3) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `product_on_sale_id` (`product_on_sale_id`,`day_of_week`,`time_of_day`),
CONSTRAINT `productTimes_productsOnSale_fk` FOREIGN KEY (`product_on_sale_id`) REFERENCES `products_on_sale` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1985 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
and the query
from product_times as pt
join products_on_sale as pos
on pos.id = pt.product_on_sale_id
and pos.product_range_id = 199
where pt.day_of_week < 'Saturday'
I can't seem to work out what I think should be easy.