getting data from multiple tables in mysql

Question

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 :(

Can you post your sample data in textual form? Images are great but it's impossible to work with data from it. — peterm
– peterm, Commented Sep 2, 2013 at 6:09

peterm · Accepted Answer · 2013-09-02 07:47:38Z

2

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

edited Sep 2, 2013 at 7:47

answered Sep 2, 2013 at 6:33

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Ando Over a year ago

I think that is it! There is one thing i can't sort out MAX(written) last_written has to be only the 'done' articles but if i add AND done='1' then the count(*) does not count all the articles. Any idea how to get it running. Thanks for the query, I would never be able to do it on my own, besides it is very quick!

peterm Over a year ago

You can try conditional aggregate. See updated answer. Let me know if it helps.

Ando Over a year ago

THIS IS MAD! Thanks guys for your help on this. Seriously I wouldn't be able to do it on my own. This was great!

Ando Over a year ago

Thanks, I added the sort for the user at the bottom.

peterm Over a year ago

I didn't exactly understand how you want it, but you can try to change user_id = 1 to user_id IN(0, 1) in both subqueries and the outer select.

|

Collectives™ on Stack Overflow

getting data from multiple tables in mysql

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related