I’m trying to list the customer’s name, last name, email, phone number, address, and the title of the show they are going. I am not supposed to list duplicate names of the customer, but unfortunately, if 1 customer is seeing different shows, their name appears twice. I am still getting duplicates despite using DISTINCT and GROUP BY. What should I include to not have duplicate names of customers?
select distinct c.first_name, c.last_name, c.email, c.phone, c.address, s.title from customer c inner join ticket tk on tk.customer_id = c.customer_id inner join `show` s on s.show_id = tk.show_id group by c.first_name, c.last_name, c.email, c.phone, c.address, s.title order by c.last_name;
Advertisement
Answer
You don’t need to aggregate by title, since, as you pointed out, there could be multiple titles. Instead, remove it from group by and aggregate it via group_concat:
select c.first_name, c.last_name, c.email, c.phone, c.address, group_concat(s.title) from customer c inner join ticket tk on tk.customer_id = c.customer_id inner join `show` s on s.show_id = tk.show_id group by c.first_name, c.last_name, c.email, c.phone, c.address order by c.last_name;
You don’t need the distinct keyword either. Remember: if you want to aggregate by a field, then more often than not you need to avoid grouping by it. The fact that due to title the records have got duplicated proves that it’s a column to be aggregated.