MySQL - Script - Creating Duplicates Despite Using Distinct Function

Hi All

I built the below script in MySQL Version 5.5

use airrider_tunn3l;
select distinct
	b.customer_name as 'Flyer Name'
    ,FROM_UNIXTIME(b.timeslot) as 'Flight Date and Time'
	,i.num as 'Invoice Number'
    ,b.voucher_title as 'Product'
    ,b.minutes as 'Flight Minutes'
    ,b.origin_nb_flights as 'Flight Quantity'
    ,b.customer_num as 'Guest Number'
    ,i.paid_ttc as 'Gross'
    ,i.total_ht as 'Net'
    ,i.total_tva as 'Tax'
    ,i.payment_method as 'Pay Method'
    ,FROM_UNIXTIME(b.checkin) as 'Check In'
FROM booking b
join invoices i on i.id = b.invoice_id
order by b.customer_name, b.timeslot, i.num, b.voucher_title, b.minutes, bb.origin_nb_flights, 
b.customer_num, i.paid_ttc, i.total_ht, i.total_tva, i.payment_method, b.checkin asc

Open in new window


The output is great, however despite using the distinct function I still end up with duplicate entries, for example (duplicate in bold)

Customer Name	Flight Date and Time	Invoice Number	Product	Flight Minutes	Flight Quantity	Gueest Number	Gross	Net	Tax	Pay Method	Check In
Test Test Test	1/21/18 01:30 PM	00000228	Kids Party Extra Flyer	1.9	2	18000182	1498	1413.21	84.79	 paymentexpress	                         1/21/18 01:44 PM
[b]Test Test Test	1/21/18 01:30 PM	00000228	Kids Party Extra Flyer	1.9	2	18000182	1498	1413.21	84.79	paymentexpress	                          1/21/18 01:44 PM[/b]

Open in new window


How do I tackle the duplicates? I know this can be done in Excel but since I'm saving the script in MySQL as a view. Any help is greatly appreciated!
Stevie ZakhourAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
Look for trailing or leading spaces on each field that could cause the two rows to be seen as different.

I notice this in your output, is this a typo, or the real data, as it looks like a leading space in the data of the first row.
Pay Method     
 paymentexpress
paymentexpress 

Open in new window


»bp
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ares KurkluSoftware EngineerCommented:
You may want to look into the reason why the duplicates occur, you can change the query create a sub query and just return the result differently depending on why more than 1 records are matching to those invoices.
0
Stevie ZakhourAuthor Commented:
Thanks all, appreciate the assistance. The query is now working
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.