bloppit bloppit - 1 month ago 11
SQL Question

What am I missing this? Do I need to use JOIN or UNION or a subquery?

I'm trying to get some practice making queries with SQL.

I'm working with a playground that uses SQLite.

There are two tables:

books_north
and
books_south


Both have columns for:
id
,
title
,
author
,
genre
and
first_published


The query I'm trying is to generate a report that lists the book titles from both locations and count the total number of books with the same title.

I can't work out how to even get started with the count.

So far I have

SELECT title
FROM books_north
INNER JOIN books_south
ON books_north.title = books_south.title;


But it just says that
title
is an ambiguous column.

How do I do this? Thank you

Answer

You need UNION ALL to get the count of each title

Select Title,Count(1) as [Count]
From
(
SELECT title FROM books_north bn
union all
select title from books_south bs 
) A
Group by Title

Another approach using FULL OUTER JOIN (If your RDBMS supports)

SELECT COALESCE(bn.Title, bs.title) as title,
       ( bn.[count] + bs.[count] ) AS [Count]
FROM   (SELECT title,
               Count(1) AS [count]
        FROM   books_north
        GROUP  BY title) bn
       FULL OUTER JOIN (SELECT title,
                               Count(1) AS [count]
                        FROM   books_south
                        GROUP  BY title) bs
                    ON bn.Title = bs.Title 

Regarding your error message, Title column is present in the both the table so when you select the Title column you need to tell the compiler from which table you want to select Title column. It can be done by giving a alias name to the tables in Join