Date: 12/28/05 (MySQL Communtiy)    Keywords: mysql, database, sql

I am trying to make it so this result can be ordered by the last name of the client. However, the name is in another table thus the JOIN LEFT. That part seems to be working OK (I think), but where I am running into a problem is that the results are showing duplicates (example if invoice 10, there are 3 result rows for invoice 10, but only 1 row in the actual MySQL table "invoice_main").

(Yes, there are some name/case mismatches, but I didnt create the database.)

Thanks!

SELECT DISTINCT
invoice_main.invoice_number,
invoice_main.invoice_date,
invoice_main.due_date,
invoice_main.clientid,
invoice_main.subtotal,
invoice_main.paid_amounts,
invoice_main.payment_status,
clients.ClientLast
FROM $TableName
LEFT JOIN clients ON invoice_main.clientid=clients.ClientID
WHERE invoice_main.office_id='$office_id'
ORDER BY $SortParam
LIMIT $limit,$Limit_increment

Adding GROUP BY invoice_main.invoice_number gave me an SQL error


[UPDATE] Figured it out
LEFT JOIN clients ON invoice_main.office_id=clients.office_id AND invoice_main.clientid=clients.ClientID

Gotta thank jedifreeman and his help with JOIN a couple weeks ago. I was re-reading our AIM conversation and thought of this.

Source: http://www.livejournal.com/community/mysql/80119.html