Hi guys, I am trying to concatenate the following aliases; monday, tuesday, wednesday, thursday, friday, saturday and sunday. And I am encountering an error like

Unknown column ‘monday’ in ‘field list’

So far, this is my query, can anyone help me how to concatenate the days from monday to sunday. Thanks in advance.

SELECT schedule.subjcode, subjdescription,
  if(schedule.monday_am='', char(32), CONCAT('M: ',schedule.monday_am,'-',schedule.monday_pm,char(13))) AS monday,
  if(schedule.tuesday_am='',char(32), CONCAT('T: ',schedule.tuesday_am,'-',schedule.tuesday_pm, char(13))) AS tuesday,
  if(schedule.wednesday_am='',char(32), CONCAT('W: ', schedule.wednesday_am,'-',schedule.wednesday_pm, char(13))) AS wednesday,
  if(schedule.thursday_am='', char(32), CONCAT('TH: ', schedule.thursday_am,'-',schedule.thursday_pm, char(13))) AS thursday,
  if(schedule.friday_am='', char(32), CONCAT('F: ',schedule.friday_am,'-',schedule.friday_pm, char(13))) AS friday,
  if(schedule.saturday_am='', char(32), CONCAT('SAT: ', schedule.saturday_am,'-',schedule.saturday_pm, char(13))) AS saturday,
  if(schedule.sunday_am='', char(32), CONCAT('SUN: ', schedule.sunday_am,'-',schedule.sunday_pm)) AS sunday,
  absences.totalabsences FROM schedule INNER JOIN absences ON absences.subjectcode=schedule.subjcode
  INNER JOIN subjectinfo ON subjectinfo.subjcode=schedule.subjcode

is this MySQL?

if so, please post your create table statements for the two tables and clarify what you are trying to do. I guess you are trying to display ‘opening hours’ or ‘trading hours’ like this:

Mon: 0900 - 1700
Tues: 1000 - 1600

if so (i may well be corrected), I would store my data differently and then format the output in the scripting language.


what exactly does “concatenate the days from monday to sunday” mean?

@r937: The purpose of concatenation is for me to place the information in the report designer - Crystal Report.