Join queries properly - SQL

Question

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?

Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. [Re SQL & relational querying](https://stackoverflow.com/a/33952141/3404097) — philipxy, Nov 26 '21 at 05:29
Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For errors that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL include DDL & tabular initialization code. When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. [ask] [Help] — philipxy, Nov 26 '21 at 05:30
What DBMS are you using? You should always tag your SQL requests with your DBMS in order to only get answers that wor for you. — Thorsten Kettner, Nov 26 '21 at 06:27

score 0 · Answer 1 · edited Nov 26 '21 at 06:15

0

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

edited Nov 26 '21 at 06:15

Thorsten Kettner

89,309
7
49
73

answered Nov 26 '21 at 05:52

Rakhi

325
1
2
13

You should not use comma-separated joins anymore. They were replaced by explicit joins (`INNER JOIN`, `LEFT OUTER JOIN`, `CROSS JOIN`, etc.) almost thirty years ago. Comma joins are prone to errors, because you can mistakenly forget a join clause and thus rendering your intended inner join a cross join. And this is exactly what happens in your query. You are cross joining the two query results, thus combining every book with every co-author count. With four books of which three have co-authors you get 4 x 3 = 12 result rows. – Thorsten Kettner Nov 26 '21 at 06:41
Comma is cross join with lower precedence than keyword JOINs. Complaining about using it is not helpful. Read the manual, learn the language. It is no more prone to errors than using "sum" when "count" is meant. JOIN ON is defined as COMMA WHERE in the standard. – philipxy Nov 26 '21 at 08:19

Tickloop · Answer 2 · 2021-11-26T06:17:31.627

0

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

edited Nov 26 '21 at 06:17

answered Nov 26 '21 at 06:06

Tickloop

76
5

1

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**almost 30 years** ago) and its use is discouraged – marc_s Nov 26 '21 at 06:08
Yes, this might work. For this query to be valid, the title must not be nullable and be unique in the table in order to uniquely identify a row in the books table. Maybe it is unique, maybe it is not. It would seem safer to group by the ISBN, which I assume to meet this requirement. – Thorsten Kettner Nov 26 '21 at 06:32

score 0 · Accepted Answer · answered Nov 26 '21 at 06:22

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;

Join queries properly - SQL

3 Answers3