MySQL – Subquery Not Recognizing Join Table Field

group-concatenationMySQLsubquery

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

Best Answer

Your derived table is missing the extra field it is looking for try this: 

SELECT journeydata.dateTime,
alarmDetails
FROM 
journeydata
LEFT JOIN
    ( SELECT 
         GROUP_CONCAT( DISTINCT(
              IF(fence.fenceName IS NULL, alarmType.alarmName, 
                   CONCAT(alarmType.alarmName, 
                        '(',fence.fenceName,')'))
         )) AS alarmDetails,
         alarm.journeydataID as journeydataID   -- <<< ADDED
      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

There might be more that needs fixing, but I would need a little time to look over the query and information you have provided. Try this for now.

EDIT: Added the (untested) distinct grouping and formatting of alarmDetails as requested.

Related Question