MySQL Forums
Forum List  »  General

Returning Unique Records
Posted by: Vernon
Date: April 12, 2006 02:02PM

First let me describe what I am trying to do. I want to join three different tables: customer_ads, receivables, & customers. I want to return every row from the customer_ads table. The customer_ads table will always have a link between the customers table on customer_ads.client_id = customers.customers_id, however there will not always be records that exist in the receivables table, but I still want to return empty values for the receivables fields I select.

Using the following code works fine:

LEFT JOIN receivables ON (customer_ads.client_id = receivables.client_id)
LEFT JOIN customers ON (customer_ads.client_id = customers.customers_id)
customer_ads.client_id = '41' AND customer_ads.start_date BETWEEN '2006-01-01' AND '2006-01-31'

It returns the correct amount of rows, 12.

However when I add receivables.cust_check_num to the SELECT statement as I want to retrieve that value, if it exisit, it returns over 100 rows with multiple copies of the same record.

Can anyone please tell me first why it returns the same records over and over and secondly how do I return only unique records.

Options: ReplyQuote

Written By
Returning Unique Records
April 12, 2006 02:02PM
April 12, 2006 05:53PM
April 12, 2006 06:28PM
April 12, 2006 06:39PM
April 19, 2006 07:09AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.