Skip to main content
added 26 characters in body
Source Link
ypercubeᵀᴹ
  • 98.7k
  • 13
  • 215
  • 305

@ypercube YperCube's answer did the job. Easy. Effective. A special thanks to him.

Anyway, after some research I have came up with an answer of my own. This is an alternate way, Just thought of sharing it.

SELECT com.id AS complaint_id,
       com.member_id AS parent_mem_id,
       crep.mem_id AS last_replier,
       crl.last_posted_dt
FROM complaints com
LEFT JOIN complaints_reply crep 
  ON com.id=crepid = crep.complaint_id
JOIN
  (SELECT cr.complaint_id,
          max(CONCAT(cr.posted_date,'_',cr.posted_time)) AS last_posted_dt
   FROM complaints_reply cr,
        complaints c
   WHERE cr.complaint_id=ccomplaint_id = c.id
     AND cr.mem_id !=c= c.member_id
   GROUP BY cr.complaint_id
  ) crl 
  ON  CONCAT(crep.posted_date,'_',crep.posted_time)=crl = crl.last_posted_dt
  AND crep.complaint_id=crlcomplaint_id = crl.complaint_id ;

@ypercube YperCube's answer did the job. Easy. Effective. A special thanks to him.

Anyway, after some research I have came up with an answer of my own. This is an alternate way, Just thought of sharing it.

SELECT com.id AS complaint_id,
       com.member_id AS parent_mem_id,
       crep.mem_id AS last_replier,
       crl.last_posted_dt
FROM complaints com
LEFT JOIN complaints_reply crep ON com.id=crep.complaint_id
JOIN
  (SELECT cr.complaint_id,
          max(CONCAT(cr.posted_date,'_',cr.posted_time)) AS last_posted_dt
   FROM complaints_reply cr,
        complaints c
   WHERE cr.complaint_id=c.id
     AND cr.mem_id!=c.member_id
   GROUP BY cr.complaint_id)crl ON CONCAT(crep.posted_date,'_',crep.posted_time)=crl.last_posted_dt
AND crep.complaint_id=crl.complaint_id

@ypercube YperCube's answer did the job. Easy. Effective. A special thanks to him.

Anyway, after some research I have came up with an answer of my own. This is an alternate way, Just thought of sharing it.

SELECT com.id AS complaint_id,
       com.member_id AS parent_mem_id,
       crep.mem_id AS last_replier,
       crl.last_posted_dt
FROM complaints com
LEFT JOIN complaints_reply crep 
  ON com.id = crep.complaint_id
JOIN
  (SELECT cr.complaint_id,
          max(CONCAT(cr.posted_date,'_',cr.posted_time)) AS last_posted_dt
   FROM complaints_reply cr,
        complaints c
   WHERE cr.complaint_id = c.id
     AND cr.mem_id != c.member_id
   GROUP BY cr.complaint_id
  ) crl 
  ON  CONCAT(crep.posted_date,'_',crep.posted_time) = crl.last_posted_dt
  AND crep.complaint_id = crl.complaint_id ;
Source Link
Sajeev C
  • 163
  • 1
  • 6

@ypercube YperCube's answer did the job. Easy. Effective. A special thanks to him.

Anyway, after some research I have came up with an answer of my own. This is an alternate way, Just thought of sharing it.

SELECT com.id AS complaint_id,
       com.member_id AS parent_mem_id,
       crep.mem_id AS last_replier,
       crl.last_posted_dt
FROM complaints com
LEFT JOIN complaints_reply crep ON com.id=crep.complaint_id
JOIN
  (SELECT cr.complaint_id,
          max(CONCAT(cr.posted_date,'_',cr.posted_time)) AS last_posted_dt
   FROM complaints_reply cr,
        complaints c
   WHERE cr.complaint_id=c.id
     AND cr.mem_id!=c.member_id
   GROUP BY cr.complaint_id)crl ON CONCAT(crep.posted_date,'_',crep.posted_time)=crl.last_posted_dt
AND crep.complaint_id=crl.complaint_id