Skip navigation links

MySQL Forums :: General :: Returning Unique Records


Advanced Search

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 Vernon 04/12/2006 02:02PM
Re: Returning Unique Records Bob Field 04/12/2006 05:53PM
Re: Returning Unique Records Vernon 04/12/2006 06:28PM
Re: Returning Unique Records Bob Field 04/12/2006 06:39PM
Re: Returning Unique Records Vernon 04/19/2006 07:09AM
Re: Returning Unique Records Peter Brawley 04/19/2006 10:14AM


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.