Skip to content

How to not include duplicates in SQL with inner join?

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;

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.