CODEWITHSUNDEEP
mysqlsql
Ando
Ando

Reputation: 1827

getting data from multiple tables in mysql

My goal is to get from the following tables - the user's unique group names and ids, the latest comments for the user's groups, the latest "done" article for the user's groups, the SUM of done articles, and total articles. Basically what is presented in the bottom sheet.

problem

So far I've managed to get the data from the groups table and from the articles, but I can't get the latest comment.

Here is my query

SELECT  `groups`.`name` ,  `groups`.`id` , (

SELECT MAX(  `articles`.`written` ) 
FROM  `articles` 
WHERE  `group` =  `groups`.`id` 
AND  `articles`.`done` =  '1'
) AS latestArt, (

SELECT MAX(  `comments`.`date_added` ) 
FROM  `comments` 
WHERE  `comments`.`article_id` =  `a`.`id` 
AND  `comments`.`active` =  '1'
) AS latestComm, SUM(  `a`.`done` =  '1' ) articlesAchieved, COUNT(  `a`.`id` ) AS totalArticles
FROM  `groups` 
LEFT JOIN  `articles` AS  `a` ON  `a`.`group` =  `groups`.`id` 
LEFT JOIN  `comments` AS  `c` ON  `c`.`note_id` =  `a`.`id` 
WHERE  `groups`.`user_id` =  '6'
AND  `n`.`active` =  '1'
GROUP BY  `groups`.`id`

I've also tried to get the data by joining everything to the article table but I wasn't successful with that either :(

Upvotes: 0

Views: 86

Answers (1)

peterm
peterm

Reputation: 92785

UPDATED Your query might look like this

SELECT g.id group_id, g.name group_name,
       a.last_written, a.total_articles, a.total_done,
       c.last_comment
  FROM groups g LEFT JOIN 
(
    SELECT `group`, 
            MAX(CASE WHEN done = 1 THEN written END) last_written,
            COUNT(*) total_articles,
            SUM(done) total_done
      FROM articles
     WHERE active = 1
       AND user_id = 1
     GROUP BY `group`
) a 
    ON g.id = a.`group` LEFT JOIN 
(
    SELECT a.`group`,
           MAX(date_added) last_comment
      FROM commants c JOIN articles a
        ON c.article_id = a.id
     WHERE a.active = 1
       AND a.user_id = 1
     GROUP BY a.`group`
) c
    ON g.id = c.`group`
 WHERE  user_id =  1

Upvotes: 2

Related Questions