MySQL: Choice from two tables (one to many)



  • There are two tables: the authors of books and the books themselves. We need to pick 10 authors with over 30, but less than 40 years old, with all their books. I gave you this request:

    SELECT 
        author.name, 
        books.title 
    FROM 
        `author` 
    LEFT JOIN 
        `books` 
    On 
        books.author_id = author.id 
    WHERE 
        author.age > 30 AND 
        author.age < 40 
    GROUP BY 
        author.name 
    LIMIT 10; 
    

    (both here) http://pastebin.com/XdhZRVz3 )

    But the problem is that only one of the author's book turns out that way, and that's all. Please help me. ♪ ♪



  • http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat :

       SELECT a.name, 
              GROUP_CONCAT(b.title) books 
         FROM authors a 
    LEFT JOIN books b ON b.author_id = a.id 
        WHERE a.age BETWEEN 30 AND 40 
     GROUP BY a.name 
        LIMIT 10; 
    

    Request:

       SELECT a.name,
              b.title
         FROM authors a
    LEFT JOIN books b ON b.author_id = a.id
        WHERE a.id IN (SELECT id
                         FROM authors
                        WHERE age BETWEEN 30 AND 40
                        LIMIT 10);
    

    Second option of request:

       SELECT a.name,
              b.title
         FROM (SELECT id,
                      name
                 FROM authors
                WHERE age BETWEEN 30 AND 40
                LIMIT 10) a
    LEFT JOIN books b ON b.author_id = a.id;
    

    By the way, the relationship between the authors and the books is many, many. Because one book can be co-authored by a few people.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2