This specific SQL exercise is haunting me all the time. I spend more than 10 hours trying to solve it but I can not come up with the solution. My SQL code is:
/* selecting all existing albums and their songs */
WITH albums AS (
SELECT a.title, t.track_id from album a
INNER JOIN track t ON t.album_id=a.album_id
/* selecting all customer purchases with their songs and album names */
customer_purchases AS (
SELECT c.first_name|| ’ '|| c.last_name customer_name, i.invoice_id, a.track_id, a.title
FROM CUSTOMER c
INNER JOIN invoice i ON c.customer_id=i.customer_id
INNER JOIN invoice_line il ON i.invoice_id=il.invoice_id
INNER JOIN albums a ON il.track_id=a.track_id
I have two tables, the first one with all songs in each album and the next table returns all the purchases of customers with an album name. I am using customer name and album names to easily understand the result. If I perform
albums EXCEPT customer_purchases, I end up with only those songs/tracks that have never been purchased. How do I create a table that shows all tracks, whether bought or not, of each album for each customer?
If I am on the wrong track, what type of two tables should I create to be able to use EXCEPT to get the number of album purchases?
I checked the answer and it has a different approach. I think my approach should also work if I only could understand how to combine two tables when there is no common key that would indicate whether this albums all songs were purchased by a customer.
Thanks for your help!