This is not working. My column names include Mon, Tue, Wed, Thu, Fri, Sat, Sun set to 0 or 1. I am trying to see if column name is 0 or 1 based on the 3-digit current day of the week.
SET @var = DATE_FORMAT(NOW(), '%a')
SELECT *, @var
FROM team LEFT JOIN fav ON team.Tag = fav.Tag WHERE ID = '123456' AND @var = '1'
ORDER BY team.Name
The variable's @var value is a string literal and can't be used as a column.
You can do what you want with a CASE expression:
SET @var = DATE_FORMAT(NOW(), '%a');
SELECT *, @var
FROM team LEFT JOIN fav
ON team.Tag = fav.Tag
WHERE ID = '123456'
AND '1' = CASE @var
WHEN 'Mon' THEN Mon
WHEN 'Tue' THEN Tue
WHEN 'Wed' THEN Wed
WHEN 'Thu' THEN Thu
WHEN 'Fri' THEN Fri
WHEN 'Sat' THEN Sat
WHEN 'Sun' THEN Sun
END
ORDER BY team.Name;
Or without the variable:
SELECT *
FROM team LEFT JOIN fav
ON team.Tag = fav.Tag
WHERE ID = '123456'
AND '1' = CASE DATE_FORMAT(NOW(), '%a')
WHEN 'Mon' THEN Mon
WHEN 'Tue' THEN Tue
WHEN 'Wed' THEN Wed
WHEN 'Thu' THEN Thu
WHEN 'Fri' THEN Fri
WHEN 'Sat' THEN Sat
WHEN 'Sun' THEN Sun
END
ORDER BY team.Name;
Inside the CASE expression qualify the columns Mon, Tue, Wed, Thu, Fri, Sat, Sun with the table (team or fav) they belong.
SELECT ..., @var part doesn't really help here, nor the static '1', but the CASE is definitely the way to tackle this problem.
SELECT ..., CASE DATE_FORMAT(NOW(), '%a')... instead, or even better, DAYOFWEEK(NOW()) instead of the DATE_FORMAT excursion.SET @var = DATE_FORMAT(NOW(), '%a'); then the variable could be used in place where a column like Mon can be used. This is the reason of the use of the variable. Sure the code can be written without it by using a function inside the CASE statement.
SET? Why not just put that in the condition?SELECT *, '1'for Monday. Consider making your data properly relational so you can do this query without a lot of fuss.