Practice Quiz – Writing Queries: Is my JOIN usage correct?

[Admin]

Here is the problem:

All of the questions in this quiz pull from the open source Chinook Database. Please refer to the ER Diagram below and familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Find the first and last name of any customer who does not have an invoice. Are there any customers returned from the query?

Source: https://www.coursera.org/learn/sql-for-data-science

Here is my tentative solution:

SELECT customers.FirstName, customers.LastName, COUNT(invoices.invoiceId)
FROM customers 
JOIN invoices
ON customers.CustomerId  = invoices.CustomerId
GROUP BY Customers.CustomerId
HAVING COUNT(invoiceId) IS NULL

Seeking help as it seems my JOIN query might not be correct.

---

Reply

---

COUNT differs from the other aggregate functions, it never returns NULL. You need to use COUNT(...) = 0 instead.

You’re trying to find any customers without invoices, but when you use an INNER JOIN it will only return customers that have rows in the invoice table. You need to use LEFT JOIN. https://www.tutorialspoint.com/sql/sql-left-joins.htm

So revised query:

[Author]

Posts