Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Posted by Laurence kite
Laurence kite
11,768 Points

SQL queries

I am attempting do answer some the practice questions in the sql playground set by Chalkers but having problems get the exact output

Here is the practice question :

-- Generate a report that lists the book titles from both locations and count the total number of books with the same title.

Here is my attempt 

  SELECT books_north.title FROM books_north
    UNION ALL
  SELECT  books_south.title FROM books_south
    UNION ALL
  SELECT COUNT(books_north.title) AS "Duplicate Books Count" FROM books_north
    INNER JOIN books_south ON books_north.title = books_south.title;

    ```


  This does not list the Duplicate Book Count as a heading but just the total this being 12?

This is my output :


title
A Brief History of Time
Armada
Emma
Frankenstein
Harry Potter and the Chamber of Secrets
Harry Potter and the Deathly Hallows
Harry Potter and the Goblet of Fire
Harry Potter and the Half-Blood Prince
Harry Potter and the Order of the Phoenix
Harry Potter and the Philosopher's Stone
Harry Potter and the Prisoner of Azkaban
Pride and Prejudice
Ready Player One
The Martian
The Universe in a Nutshell
A Brief History of Time
Congo
Emma
Frankenstein
Harry Potter and the Chamber of Secrets
Harry Potter and the Deathly Hallows
Harry Potter and the Goblet of Fire
Harry Potter and the Half-Blood Prince
Harry Potter and the Order of the Phoenix
Harry Potter and the Philosopher's Stone
Harry Potter and the Prisoner of Azkaban
Jurassic Park
Pride and Prejudice
Sphere
The Universe in a Nutshell
12

I want the last bit to have the heading Duplicate Book Count but not sure how to achieve this?

I have tried using NULLs but this doesn't really help
Steven Parker
Steven Parker
231,198 Points
Steven Parker
Steven Parker
231,198 Points

Can you provide a link to the course page you are working with?

5 Answers

Steven Parker
Steven Parker
231,198 Points
Steven Parker
Steven Parker
231,198 Points

When you perform a UNION, the first query establishes what the quantity, names, and types of the output columns will be. All others being UNIONed must match in number and types of columns, and any other names are ignored.

I can probably give you more specific help once I see what you're working on.

Update: Now that I've seen the challenge, I think that "total" they are asking for is per title. So your output should look something like this (I named the "total" column "Copies"):

Title                                      Copies
-----                                      ------
A Brief History of Time                    2
Armada                                     1
Congo                                      1
Emma                                       2
Frankenstein                               2
Harry Potter and the Chamber of Secrets    2
Harry Potter and the Deathly Hallows       2
Harry Potter and the Goblet of Fire        2
Harry Potter and the Half-Blood Prince     2
Harry Potter and the Order of the Phoenix  2
Harry Potter and the Philosopher's Stone   2
Harry Potter and the Prisoner of Azkaban   2
Jurassic Park                              1
Pride and Prejudice                        2
Ready Player One                           1
Sphere                                     1
The Martian                                1
The Universe in a Nutshell                 2

You can do this with JOINs or UNIONs, and you will probably also want to use GROUP BY.

Laurence kite
Laurence kite
11,768 Points
Laurence kite
Laurence kite
11,768 Points

SELECT title, COUNT(title) AS Copies FROM
(SELECT title FROM books_south UNION ALL SELECT title FROM books_north) AS allb GROUP BY title;

A Brief History of Time 2 Armada 1 Congo 1 Emma 2 Frankenstein 2 Harry Potter and the Chamber of Secrets 2 Harry Potter and the Deathly Hallows 2 Harry Potter and the Goblet of Fire 2 Harry Potter and the Half-Blood Prince 2 Harry Potter and the Order of the Phoenix 2 Harry Potter and the Philosopher's Stone 2 Harry Potter and the Prisoner of Azkaban 2 Jurassic Park 1 Pride and Prejudice 2 Ready Player One 1 Sphere 1 The Martian 1 The Universe in a Nutshell 2

I would like to know how to use a join to get the results

Steven Parker
Steven Parker
231,198 Points
Steven Parker
Steven Parker
231,198 Points

Sorry, I was thinking of FULL OUTER JOIN, which is not supported by the playground. :disappointed:
But, you got it! :+1:

Laurence kite
Laurence kite
11,768 Points
Laurence kite
Laurence kite
11,768 Points

HI there thanks for getting back to me

Here is the link to the course :

https://teamtreehouse.com/library/using-a-subquery-to-create-a-temporary-table-part-1

Here is the bit I am struggling with its a playground of testing sql

https://teamtreehouse.com/sql_playgrounds/522#/queries/670ffc6e-68f8-4117-aeda-a3bd5d37d4b3

This is my revised code 

SELECT books_north.title AS "Book Titles Both", COUNT(books_north.title) AS "Duplicate Books Count" FROM books_north
    INNER JOIN books_south ON books_north.title = books_south.title
   UNION ALL 
  SELECT books_north.title, NULL FROM books_north
    UNION ALL
  SELECT  books_south.title, NULL  FROM books_south;

I really wanted the Duplicate Books Count displayed at the bottom, I am not sure if what I have done produces the requested results

Which are :

-- Generate a report that lists the book titles from both locations and count the total number of books with the same title.

Laurence kite
Laurence kite
11,768 Points
Laurence kite
Laurence kite
11,768 Points

I am not sure how to do it with JOINS as I am new to this and JOINS seem to find matching rows and not books that are just in one table..

Any hints lol

Steven Parker
Steven Parker
231,198 Points
Steven Parker
Steven Parker
231,198 Points

Disregard the comments about JOIN, you can use UNION ALL to create a derived table of just titles, and then an outer query using COUNT and GROUP BY to make the final list like the one shown in my answer.

Laurence kite
Laurence kite
11,768 Points
Laurence kite
Laurence kite
11,768 Points 
A Brief History of Time 2
Armada  1
Congo   1
Emma    2
Frankenstein    2
Harry Potter and the Chamber of Secrets 2
Harry Potter and the Deathly Hallows    2
Harry Potter and the Goblet of Fire 2
Harry Potter and the Half-Blood Prince  2
Harry Potter and the Order of the Phoenix   2
Harry Potter and the Philosopher's Stone    2
Harry Potter and the Prisoner of Azkaban    2
Jurassic Park   1
Pride and Prejudice 2
Ready Player One    1
Sphere  1
The Martian 1
The Universe in a Nutshell  2