Re: Returning Unique Records

[Vernon]

> Your new request, for a query to "join the three tables without
> returning multiple rows with the same value and that are older than
> 90 days old", needs clarification on what should not be duplicated.

What I mean is I don't want the same orders showing up over and over. For instance the customer_ads.id is the actual order #. When I use the select statement as follows:

SELECT DISTINCT
customer_ads.id,
customer_ads.start_date

there are only 12 recrods returned. But when I use this:

SELECT DISTINCT
customer_ads.id,
customer_ads.start_date,
receivables.cust_check_num

which is to allow me to also display the cust_check_num as well, then I'll get the same orders repeated like 10 times or so and then the next record repeated as well and forth until I have hundreds, many of which being the same record, are records when I should only have 12

> In the receivables table, is there one row per customer, or might
> there be multiple rows per customer, or are the receivables related
> 1:1 or many:1 to cusromer_ads, or what? And if there are multiple
> rows per customer, do you want a sum, or all the rows, or all the
> rows more than 90 days old, or what?

First off the dates are selected by the user and works fine with a BETWEEN statement. The receivables table is related many to 1. And what I want is to see if a value exist for 'receivables.cust_check_num' and based on the user's choice on the previous page either display the related rows based on weather or not the value is null.

I've tired the following and have gottten the correct amount of records returned however receivables.cust_check_num value is blank for all of them:

SELECT
customer_ads.id,
customer_ads.client_id,
customers.company,
customer_ads.start_date,
receivables.cust_check_num
FROM
customer_ads
LEFT JOIN receivables ON (customer_ads.id = receivables.recordID)
LEFT JOIN customers ON (customer_ads.client_id = customers.customers_id)
WHERE
(customer_ads.client_id = '41') AND
(customer_ads.start_date >= '2006-01-01' AND customer_ads.start_date <= '2006-01-31')