-1

How do I find the customers who have bought music from every genre in the list?

enter image description here

I am trying inner join and trying recursive statements.

SELECT CONCAT(customer.first_name,' ',customer.last_name), genre.name 
FROM customer 
JOIN invoice
ON customer.customer_id = invoice.customer_id
JOIN invoice_line
ON invoice.invoice_id = invoice_line.invoice_id 
JOIN track
ON invoice_line.track_id = track.track_id
JOIN genre
ON track.genre_id = genre.genre_id
WHERE name in (SELECT name FROM genre);
philipxy
  • 14,867
  • 6
  • 39
  • 83
Gaganpreet Kaur
  • 1
  • [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) An ERD is an image of DDL. – philipxy Mar 29 '23 at 01:31
  • Please ask 1 specific researched non-duplicate question. Please either ask re 1 bad query/function with obligatory [mre], including why you think it should return something else or are unsure at the 1st subexpression that it doesn't give what you expect or are stuck, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do with justification & ideally a [mre]. Then misunderstood code doesn't belong. But please ask about unexpected behaviour 1st because misconceptions get in the way of your goal. [ask] [Help] Basic questions are faqs. – philipxy Mar 29 '23 at 01:32
  • [check if a column contains ALL the values of another column - Mysql](https://stackoverflow.com/q/28939367/3404097) [Select values that meet different conditions on different rows?](https://stackoverflow.com/q/477006/3404097) – philipxy Mar 29 '23 at 06:12
  • Clearly your goal is a faq.Please before considering posting: Pin down code issues via [mre]. Read manuals/references & google error messages & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected?](https://meta.stackoverflow.com/q/261592/3404097) Reflect research in posts. – philipxy Mar 29 '23 at 06:14
  • # lets see the data SELECT * FROM cust; # lets use with to find out the customers who have bought music from more that 10 genres WITH cust1 AS ( SELECT customer_name, genre_name, ROW_NUMBER() OVER( PARTITION BY customer_name ORDER by genre_name) AS num FROM cust) SELECT customer_name, COUNT(*) as number_of_genres FROM cust1 GROUP BY customer_name ORDER BY number_of_genres DESC ; – Gaganpreet Kaur Mar 29 '23 at 21:52
  • Please clarify via edits, not comments. Please act on all feedback. PS I have no idea why you commented that. – philipxy Mar 29 '23 at 23:42

2 Answers2

0

You can try this query to get customers who have bought music from every genre :

SELECT c.customer_id
FROM customer c
JOIN invoice i ON c.customer_id = i.customer_id
JOIN invoice_line il ON i.invoice_id = il.invoice_id 
JOIN track t ON il.track_id = t.track_id
JOIN genre g ON t.genre_id = g.genre_id
group by c.customer_id
having count(*) = count(distinct g.genre_id)

So to get the first name and last name you can do as follows :

select CONCAT(c.first_name,' ',c.last_name)
from customer c
inner join (
  SELECT c.customer_id
  FROM customer c
  JOIN invoice i ON c.customer_id = i.customer_id
  JOIN invoice_line il ON i.invoice_id = il.invoice_id 
  JOIN track t ON il.track_id = t.track_id
  JOIN genre g ON t.genre_id = g.genre_id
  group by c.customer_id
  having count(*) = count(distinct g.genre_id)
) s on s.customer_id = c.customer_id
SelVazi
  • 10,028
  • 2
  • 13
  • 29
  • /* Q. Find out if there are any customers who bought music from every genre. Which city they belong to ? */ # lets find out how many genres are there SELECT COUNT(*) FROM genre; # There are total 25 genres – Gaganpreet Kaur Mar 29 '23 at 21:51
  • You can do somthing like : *select CONCAT(c.first_name,' ',c.last_name), c.city from …* – SelVazi Mar 29 '23 at 22:07
0

Q. Find out if there are any customers who bought music from every genre. Which city they belong to ? lets find out how many genres are there

    SELECT COUNT(*) FROM genre;

There are total 25 genres lets use joins to join the tables genre with the invoice information lets create a view named as cust that stores the joined data

    CREATE VIEW cust AS 
    SELECT CONCAT(customer.first_name,' ',customer.last_name) AS customer_name, 
    genre.name AS genre_name
    FROM customer 
    JOIN invoice ON
    customer.customer_id = invoice.customer_id
    JOIN invoice_line ON
    invoice.invoice_id = invoice_line.invoice_id 
    JOIN track ON 
    invoice_line.track_id = track.track_id
    JOIN genre ON
    track.genre_id = genre.genre_id
    GROUP BY customer_name, genre_name
    ORDER BY customer_name ASC;

lets see the data

     SELECT * FROM cust;

lets use with to find out the customers who have bought music from more that 10 genres

     WITH cust1 AS 
     (
     SELECT 
     customer_name,
     genre_name,
     ROW_NUMBER() OVER( PARTITION BY customer_name ORDER by genre_name) AS num
     FROM cust)
     SELECT 
     customer_name, COUNT(*) as number_of_genres
     FROM cust1
     GROUP BY customer_name
     ORDER BY number_of_genres DESC 
     LIMIT 10;
Gaganpreet Kaur
  • 1