CODEWITHSUNDEEP
mysql
user3258571
user3258571

Reputation: 384

Using a variable in WHERE statement column name

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

Upvotes: 0

Views: 185

Answers (1)

forpas
forpas

Reputation: 164204

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.

Upvotes: 2

Related Questions