I have a three tables. The main table is the journeydata and next table is the alarm tables. The primary key for journeydata is journeydataID and its available as key in the alarm tables. Next in the alarm table there is a fenceID which is a primary key of another table that is fence table. Below I am trying to run a subquery example.What I am trying to do is join all the alarm for a particular journeydataID if it exist into a single string. But I keep getting error #1054 - Unknown column 'alarmgroup.journeydataID' in 'on clause'. Next my task I want to show is a alarm has a fence name value. Example if there is a relevant value of a alarmName with fenceName show as alarmName(fenceName).
SELECT journeydata.dateTime, alarmDetails FROM journeydata LEFT JOIN ( SELECT GROUP_CONCAT(alarmType.alarmName) AS alarmDetails FROM alarm JOIN alarmType ON alarm.alarmTypeID = alarmType.alarmTypeID LEFT JOIN fence ON alarm.fenceID=fence.fenceID WHERE alarm.associateID=6 GROUP BY alarm.journeydataID ) AS alarmgroup ON journeydata.journeydataID=alarmgroup.journeydataID WHERe journeydata.associateID=6