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.