1

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
  • 1
    Please do not update a question with answers. I have rolled it back. – ypercubeᵀᴹ Sep 17 '18 at 19:57
  • @ypercubeᵀᴹ sorry for that cause I was thinking to make easier for voltaire to assist further. – user8012596 Sep 18 '18 at 4:44
  • Thank you. If the problem is solved, you can accept the answer that helped you. If the problem is not solved, you can edit the question and add more details below the existing text. Just don't correct the existing code, as it makes the answer look irrelevant. – ypercubeᵀᴹ Sep 18 '18 at 7:37
  • If the problem is solved but you now face a new problem (after fixing this one), you can accept the answer and post a new question. – ypercubeᵀᴹ Sep 18 '18 at 7:38
  • No I have added further problem in the first time itself the questioned asked.You can see it here "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)." – user8012596 Sep 18 '18 at 18:24
2

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.

  • yes its working when I added this line alarm.journeydataID as journeydataID you miss the extra D I added but why I need that not quite sure. How about the fenceName how to combine it with the alarmname if there is a fence name ? – user8012596 Sep 17 '18 at 15:49
  • I have updated my question with you answer but I have edited the this GROUP_CONCAT(alarmType.alarmName,fence.fenceName) AS alarmDetails, What I notice is that now only those alarm with which have matching fenceName appear others dont appear. What I want to do is that for those no fence name just show the alarmname but those which have fencename to show as alarmName(fenceName) – user8012596 Sep 17 '18 at 15:55
  • @ypercubeᵀᴹ is right btw, Best to accept the answer and create a new question. Keeps the original question and answer simple. Also helps out us low-reputation guys climb the reputation ladder. – Voltaire Sep 19 '18 at 11:09
  • 1
    I liked @ypercubeᵀᴹ's suggestion of using COALESCE() instead of IF(), i.e. COALESCE(CONCAT(alarmType.alarmName,'(',fence.fenceName,')'), alarmType.alarmName). Had to swap around some stuff, but this works because CONCAT() of a NULL field returns NULL overall. – Voltaire Sep 19 '18 at 11:14
  • yes the coalesce seems to be working meanwhile I was trying the if else statement. Which is better and efficient ? – user8012596 Sep 23 '18 at 8:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.