Join queries properly - SQL-CodePudding

I have two tables called Books and Co-author. I would like to join them to get the table displayed under "Desired output". I'm new to SQL and I'm struggling to join two query's that I made...

Books:

ISBN	title	author
1111	Book1	author1
2222	Book2	author2
3333	Book3
4444	Book4	author3

Co-author:

id	author	book(isbn)
1	author_x	4444
2	author_y	1111
3	author_z	2222
4	author_w	4444

Desired output:

title	has_author	count_co_author
Book1	1	1
Book2	1	1
Book3	0	0
Book4	1	2

I have the following queries:

SELECT b.title, count(c.book)
FROM Books b
LEFT JOIN Coauthor c ON b.isbn = c.book
GROUP BY b.title

which returns the column count_co-author.

And another query for the column has_author:

SELECT 
    b.title, 
    CASE 
        WHEN b.author IS NULL 
            THEN 0 
            ELSE 1 
    END AS 'Has author'
FROM Books b

How do I combine them?

CodePudding user response：

Select the books and join the co-author count:

select
  b.title,
  case when b.author is null then 0 else 1 end as has_author,
  coalesce(c.cnt, 0) as count_co_author
from books b
left outer join
(
  select book, count(*) as cnt
  from co_author
  group by book
) c on c.book = b.isbn
order by b.title;

Or select from books and get the co-author count in a subquery:

select
  b.title,
  case when b.author is null then 0 else 1 end as has_author,
  (
    select count(*)
    from co_author c
    where c.book = b.isbn
  ) as count_co_author
from books b
order by b.title;

CodePudding user response：

select  title, has_auther, count_co-author, 
from 
( 
  (
    SELECT
      b.title,
      count(c.book) as count_co-author
    FROM Books b 
    LEFT JOIN Coauthor c ON b.isbn = c.book
    GROUP BY b.title
  ) as t1, 
  (
    SELECT 
      b.title, 
      CASE WHEN b.author IS NULL 
        THEN 0 
        ELSE 1 
    END AS has_auther
    FROM Books b
  ) as t2
)

Try this one

CodePudding user response：

SELECT 
    b.title, 
    CASE
        WHEN b.author IS NULL
            THEN 0
            ELSE 1
    END AS has_author,
    count(c.book) as count-coauthor
FROM
    Books b
LEFT JOIN
    Coauthor c
ON
    b.isbn = c.book
GROUP BY
    b.title

Should work IG