I have two very simple count queries that I would like to join into one.

To clarify the situation I will add the tables I got:

Book

Author

Coauthor

And the queries I made: Query 1:

SELECT a.name, count(*) 
FROM coauthor c INNER JOIN author a ON c.name = a.code
GROUP BY a.name

Gives:

Query 2:

SELECT a.name, count(*) 
FROM author a INNER JOIN book b ON a.code = b.author
GROUP BY a.name

Gives:

And so I would like to get:

I have been searching the web, but I guess I don't know where to start.

CodePudding user response：

You can join Author to Book and Coauthor with LEFT joins and aggregate:

SELECT a.code, a.name, 
       COUNT(DISTINCT b.isbn)   COUNT(DISTINCT c.isbn) count
FROM Author a
LEFT JOIN Book b ON b.author = a.code
LEFT JOIN Coauthor c ON c.name = a.code
GROUP BY a.code, a.name;

See the demo.

CodePudding user response：

Union coauthor and book, then join those unique results to Author

SELECT q.code as Name
-- , a.name as Author
, COUNT(*) as Total
FROM
(
    SELECT name AS code, isbn
    FROM coauthor
    UNION
    SELECT author, isbn
    FROM book
) q
JOIN author a
  ON q.code = a.code
GROUP BY q.code, a.name
ORDER BY q.code

Note that normal UNION will filter the duplicate rows out.
While a UNION ALL keeps duplicate rows.