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:

SELECT DISTINCT
customer_ads.id,
customer_ads.start_date
FROM
customer_ads
LEFT JOIN receivables ON (customer_ads.client_id = receivables.client_id)
LEFT JOIN customers ON (customer_ads.client_id = customers.customers_id)
WHERE
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


Subject
Written By
Posted
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.