How to not include duplicates in SQL with inner join?-CodePudding

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;

CodePudding user response：

OK a quick check on mySQL documentation reveals that you could use Group_Concat() for your purpose:

select c.first_name, c.last_name, c.email, c.phone, c.address, group_concat(s.title) as 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;

CodePudding user response：

I have had similar queries with the same issue as you're having. This is how I would write this:

select distinct c.first_name, c.last_name, c.email, c.phone, c.address, s.title
from customer c
left join ticket tk on tk.customer_id = c.customer_id
left 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;

CodePudding user response：

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.